Finding Variations in data among different lists

[mithelesh]

Hi all

Request you to help me finding out differences across multiple lists .

Rawdata contains lists
UGPCC0180
UGPCC0181
UGPCC0182

I need to find out odd "no"'s in each column. I mean finding variations among three type of lists.

data respresented here in the sheet is having less data.


~Mithelesh.S

 

[Luke M]

Hi mithelesh,

Can you expand on what you want done with the data? I'm not sure what calculations you are wanting to perform. Are we wanting to just highlight the odd numbers? What is the relationship between the lists? What sort of variation are we looking for?

 

[mithelesh]

Hi Luke ,

There are three types of lists. Some lists are having same numbers and different numbers as mentioned in second column.

For eg: 1233456 is present in 80,81 and 82 lists.

So i want to filter or highlight or identify data that in "not unique " among all lists.

~Mithelesh.S

 

[Luke M]

With presented data, this formula will indicate items that appear in multiple lists (aka, not unique)
=COUNTIFS($A$2:$A$26,"<>"&$A2,$B$2:$B$26,$B2)>0
Could be used in a helper column to filter on, or as a conditional formatting formula.

 

[mithelesh]

Hi Luke,

Just i came to know another way of finding differences, but this is good with less amount of data. What if data is in thousands .


Please see attached file. Using PIVOT.


~Mithelesh.S

 

[Luke M]

Looks pretty good, but there could still be problems due to a number existing > 1 time within one list. But, this does give me an idea. Similar to how we find unique values in a PT, we can do a Pivot of a Pivot.

Now we can handle a large amount of data w/o formula calc overhead, and still get accurate counts.

 

[mithelesh]

Hi Luke

Checked Pivot of Pivot , that was helpful .

Thankyou
Mithelesh.S