Matching and realign column contents

[APHK]

Hi All,

I have a spreadsheet with 4 columns :

A = invoice numbers
B = invoice values
C= invoice numbers
D = invoice values

Columns A&B are List 1.
Columns C&D are List 2.

Column A and Column C contain have invoices that match and also other invoices that do not match.

I wish to have the invoice numbers in List 1 and List 2 sorted so that matched invoice numbers appear in the same row and thus I can check whether their respective invoice values also match.

I also need any unmatched invoices to remain in their respective columns, so that I can investigate further.

What I was going to do was to sort the two lists and then manually match them through cutting and pasting the two columns downwards so that matched invoice numbers appear in the same rows.

Can anyone suggest a quicker way of doing this?

[:) Sixthsense :)]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[jhren]

See if attached helps.

Note values in Columns A & B are random generated, they will change (as will dependent cells) every time the sheet is re-calc'd.

[APHK]

Matching sample.xlsx

Sample attached.

In my sample, there are blanks in each column for invoice numbers out of sequence. If this is not possible through automation, I do not mind all the unmatched invoice numbers in each column remaining out of sequence.

Thanks.

[APHK]

Sorry if my explanation of what I wanted was not clear in my original post. I need the matched invoice numbers to remain in the same row.

[jhren]

I can get close. The method results in two rows of same invoice number and values. Perhaps someone knows how to get around it...

[jhren]

Slight improvement...

First of the two duplicate rows is now a blank row... and non-matching values with the same invoice number are highlighted (red fill, white text).

[APHK]

@jhren, that is very interesting thanks. However, when I tried replacing the short list in the original sample with a much longer list the results I get from the formula remain short eventhough I copied the formula all the way down.

Please see attached.
matching-and-realign-column-contents_APHK.xlsx

[jhren]

Expand the named ranges (Formulas>Name Manager). Highlight one at a time each of the four named ranges and edit the last cell row number (e.g. num_1.1 from =Sheet1!$A$4:$A$11 to =Sheet1!$A$4:$A$294 in "Refers to:" ...then press Enter (any activity outside the box should invoke a prompt to save changes; pressing Enter is just quicker). You can also change the last cell row to some exorbitant number if you want... like 10000 (or more).

[jhren]

Also found conditional formatting errors. Happens without warning when "moving" stuff around. Haven't got the gist of how/when it happens myself. Just happen to notice same invoice number having different values.

[APHK]

Great. That is the kind of result I am looking for. Many thanks for taking the time.