What si different in each column

[cpitc]

I have 2 columns in Excel. They are list of Invoice numbers.

In one column I have a difference of 5 numbers in total rows(Column A as 2270 rows and Column B as 2275 rows). How can I tell which ones are different without having to go through nearly 3000 rows

Thanks

 

[bluedragon2]

QQuick way, sort them and look where they are off

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[cpitc]

Thanks for the reply. I dont think there are adifferent numbers, but some invoice numbers have more rows than the other and this is wehre the difference is happening. SO to scroll down nearly 3000 rows is quite bad.

I am thinking maybe a count may do the trick but not sure how to apply it. Any ideas

 

[SkipVought]

Hi,

COUNTIF function.

First create 2 UNIQUE lists of invoices using Data > Advanced filter in another location. Sort into order. Compare COUNTIF on each list.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cpitc]

Thanks Skipvought

I ahve tried this but dont quite understand the Countif part.

I think the problem is the invoice numbers are the same all the way down, but some have more rows in one list than they do in the orther, so i need to find out which are the ones with different row amounts.

Not sure ow to use the Countif, could you give me an example perhaps.

Thanks

 

[SkipVought]

Did you look at Excel HELP on COUNTIF?

Another option would be to do a PivotTable on each one and place the PTs side by side.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Once you know "which are different" what do you want to do?

IF you will add any missing data then
method A might be helpful.
If you just want to identify the differences then either a pivottable (Method B) or, my favourite, Method C using CountIF. C has the advantage of allowing you to filter on the results if there is additional data in columns you haven't told us about.

(All of these have essentially been suggested already, just thought I would spell out a bit more how I would implement them)

Method A

Sort each column
In column C use the formula (cell c1) =A1=B1
Copy down

Your two columns get out of sync where the result is False
At that point insert a cell, Shift down in whichever list is missing a number. Re-copy the formula down the list, scroll down to the next False and repeat.

Or Method B]:
Start with
[tt]A1 A1
A2 A3
A3 A4
A4 A5
A4 A6
A5 A7
A6 A8
A7 A9
A8 A10
A9

[/tt]Then go through these steps

Insert column to the left of each column to identify it[tt]List1 A1 List2 A1
List1 A2 List2 A3
List1 A3 List2 A4
List1 A4 List2 A5
List1 A4 List2 A6
List1 A5 List2 A7
List1 A6 List2 A8
List1 A7 List2 A9
List1 A8 List2 A10
List1 A9
[/tt]

Copy and paste List2 to below List1 thus making a single 'normalised' list:[tt]List Reference
List1 A1
List1 A2
List1 A3
List1 A4
List1 A4
List1 A5
List1 A6
List1 A7
List1 A8
List1 A9
List2 A1
List2 A3
List2 A4
List2 A5
List2 A6
List2 A7
List2 A8
List2 A9
List2 A10
[/tt]
[tt]

Create a pivottable
(Reference as RowField, List as ColumnField, List as data Field)
Count of List List
Reference List1 List2 Grand Total
A1 1 1 2
A10 1 1
A2 1 1
A3 1 1 2
A4 2 1 3
A5 1 1 2
A6 1 1 2
A7 1 1 2
A8 1 1 2
A9 1 1 2
Grand Total 10 9 19
[/tt]

Reference Field set to sort by Count of List
(in XL2003 and earlier right click on the field heading, Properties, Advanced, AutoSort Ascending Using Count of Data
[tt]
Count of List List
Reference List1 List2 Grand Total
A10 1 1
A2 1 1
A1 1 1 2
A3 1 1 2
A5 1 1 2
A6 1 1 2
A7 1 1 2
A8 1 1 2
A9 1 1 2
A4 2 1 3
Grand Total 10 9 19
[/tt]

Method C
Formula in C1 =COUNTIF($B$1:$B$9,A1)
Formula in D1 =COUNTIF($A$1:$A$9,B1)
Formula in E1 =IF(OR(C1=0,D1=0),"Missing from one of the lists","In Both Lists")

[tt]A1 A1 1 1 In Both Lists
A2 A3 0 1 Missing from one of the lists
A3 A4 1 2 In Both Lists
A4 A5 1 1 In Both Lists
A4 A6 1 1 In Both Lists
A5 A7 1 1 In Both Lists
A6 A8 1 1 In Both Lists
A7 A9 1 0 Missing from one of the lists
A8 A10 1 0 Missing from one of the lists
A9 1 0 Missing from one of the lists
[/tt]


Gavin