Compare multiple column of data and list out common and unique component in adj columns

[kuansheng]

Hi! I am trying to compare multiple column in a worksheet to find
common component in all the columns and what is unique to a particular
column only. And list the results/finding in adj column. What i am
trying to accomplish is something as below.

BEFORE
Sheet1 Sheet2 Sheet3
Column2 Column2 Column2
02-1234-12 07-1234-12 02-1234-12
04-1234-12 03-1234-12 02-1234-12
05-1234-12 02-1234-12 06-1234-34

AFTER
Common to all Unique to sheet1 Unique to Sheet2
02-1234-12 05-1234-12 07-1234-12

 

[Max]

Here's one formulas play to tinker with ..

Sample construct is available at:
http://www.savefile.com/files/9916738
Compare MultiCol n List Common n Unique Items.xls

The play assumes as a startpoint, that we have combined/stacked up* the
source data in cols A to B in a new sheet, with data from row2 to row15
(say):
*via manual copy > pasting from the various sheets,
with the sheetnames filled down in col B (a one-time job)

02-1234-12 Sheet1
04-1234-12 Sheet1
05-1234-12 Sheet1
07-1234-12 Sheet2
03-1234-12 Sheet2
02-1234-12 Sheet2
02-1234-12 Sheet3
02-1234-12 Sheet3
06-1234-34 Sheet3
etc

Then ..

In D2: =IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"",ROW()))
In E2: =INDEX(A:A,MATCH(SMALL(D,ROW(A1)),D,0))

Sheetnames listed in F1:H1 : Sheet1, Sheet2, Sheet3

In F2, array-entered**, F2 copied to H2:
=IF(ISERROR($E2),"",IF(ISNA(MATCH(1,($A$2:$A$15=$E2)*($B$2:$B$15=F$1),0)),""
,MATCH(1,($A$2:$A$15=$E2)*($B$2:$B$15=F$1),0)))

**press CTRL+SHIFT+ENTER

In I2: =IF(AND(F2<>"",G2<>"",H2<>""),ROW(),"")
In J2: =IF(AND(F2<>"",G2="",H2=""),ROW(),"")
In K2: =IF(AND(F2="",G2<>"",H2=""),ROW(),"")
In L2: =IF(AND(F2="",G2="",H2<>""),ROW(),"")

In M2, M2 copied to P2
=IF(ISERROR(SMALL(I:I,ROW(A1))),"",
INDEX($E:$E,MATCH(SMALL(I:I,ROW(A1)),I:I)))

Labels placed in M11 :

Common to all
Unique to Sheet1
Unique to Sheet2
Unique to Sheet3

Then just select D22, fill down to P15
Cols M to P will return the desired results

 

[kuansheng]

This is great, Thanks!!

 

[Max]

You're welcome !

 

[kuansheng]

Hi Max,
If i have at least 3000 data in each sheet. how can i paste all this
data in the combined sheet?

 

[Max]

If i have at least 3000 data in each sheet.
how can i paste all this
data in the combined sheet?

As mentioned in my earlier response:

.. The play assumes as a startpoint,
that we have combined/stacked up* the
source data in cols A to B in a new sheet,
with data from row2 to row15 (say):
*via manual copy > pasting from the various sheets,
with the sheetnames filled down in col B (a one-time job)

... it's a one-time *manual* copy > paste of the data from each of the 3
sheets, in turn, into a new sheet, into col A, starting in A2 down. Then
manual copy > paste > fill the corresponding sheetnames into col B. Stack
up the data/sheetnames one below the other in sequence: Sheet1's, then
Sheet2's, then Sheet3's.

As cols A and B can hold up to 65K rows max, putting 3,000 x 3 sheet's worth
= 9K rows total shouldn't pose a problem. But before filling in the
formulas, best to set the calc mode to Manual (via: Tools > Options >
Calculation tab). Then just press F9 to calc/recalc when ready.

Note that you need to adapt the range in the formulas in cols F to H to suit
the extent of the actual data in cols A and B before you proceed to fill
across/down.

Assuming the extent of your actual data is A2:B9000 (say), then change the
parts : $A$2:$A$15 and $B$2:$B$15 in the array formula below in F2 to
$A$2:$A$9000 and $B$2:$B$9000, before copying F2 to H2
---------
In F2, array-entered**, F2 copied to H2:
=IF(ISERROR($E2),"",IF(ISNA(MATCH(1,($A$2:$A$15=$E2)*($B$2:$B$15=F$1),0)),""
,MATCH(1,($A$2:$A$15=$E2)*($B$2:$B$15=F$1),0)))
**press CTRL+SHIFT+ENTER
--------

 

[Max]

Sheetnames listed in F1:H1 : Sheet1, Sheet2, Sheet3

Remember to change the sheetnames listing in F1:H1
to reflect the *actual* sheetnames that you have

 

[kuansheng]

Hi Max, is there anyway that i can copied the formula down/across
without changing the value of different cell manually as this would be
tedious for 9000 row.Thanks in advance

 

[Max]

Definitely <g> ! ..

From my first response:

.. Then just select D22, fill down to P15

After you have placed/copied the top row formulas into D22 (ranges adapted
to suit as mentioned in my earlier 3rd response), then just select D22 and
drag down the "fill handle" to P9000. The fill handle is at the bottom
right corner of P2, looks like a "black square". And when you point the
cursor at this corner, it'll turn into a "black cross". The formulas will
change relatively (and correctly) when you fill down.

("Fill" has the same meaning as "copy")

One thing we might want to do though before filling down, is to set the calc
mode to Manual (as with the large amount of formulas to be filled, it's
going to be quite calculation-intensive). Click Tools > Options >
Calculation tab. The options are there. Check "Manual" > OK. Then proceed
with the fill down to P9000. When done, just press F9 to recalc (may have to
wait for a while for calc to complete. The calc status can be seen at the
bottom left of the screen. When it's complete, it should show: Ready)

(Change the calc mode back to "Automatic" thereafter, if desired)

 

[kuansheng]

Thanks so much

 

[Max]

You're welcome !

 

[kuansheng]

I have got another problem that i am hoping you could help out. I hope
i can phrase it as detail as possible. I have a master worksheet that
hold the list of inventory(bill of material) and the corresponding
quantity that i have on hand like say we name it (MasterInventory). The
value in the MasterInventory is dynamic, quantity will be deducted went
a certain component is used in the production of a product and will
increase when supply come in. The data of the supply come in the form
of another excel worksheet. It is broken down into dates that they will
be deliver. Example is as follow:

MasterInventory (Before)
model part number/description Quantity 12/1 13/1 15/1
LP120 12-1234-12 12 12 12 12
LP120 13-1234-14 05 05 05 05
M1 15-1234-12 10 10 10 10

009C 14-1234-15 01 01 01 01


SupplyData
Mon Tue Wed Thu
part number/description 12/1 13/1 15/1 17/1
12-1234-12 02 02 03 04
13-1234-14 01 00 03 01
14-1234-15 00 01 03 00

What i am trying to do is something like a postman. Sorry if i use
inappropriate terms. The SupplyData are like the letters he has to
deliver and the MasterInventory is the letter box with different pigion
hole that he can slot the letter accordingly. Meaning the quantity in
the MasterInventory will find matching part number from the SupplyData
and add up its current quantity(MasterInventory) with the new quantity
that is due to deliver(SupplyData) according to the date.

MasterInventory (After)
model part number/description Quantity 12/1 13/1 15/1
LP120 12-1234-12 12 14 16 19
LP120 13-1234-14 05 06 06 09
M1 15-1234-12 10 10 10 10

009C 14-1234-15 01 01 02 02

The reason that i am trying to do this to relief the user from data
entry as this will help to reduce human error. Thanks if you could help.

 

[Max]

Hi Kuan Sheng,

It's best to put in your new query as a *new* post.
This thread is pretty long in the tooth, and I believe it's due for closure
<g>

Posting your new query afresh will avail your new post to the radar of all
responders out there, some of whom may well have something suitable to offer
you. It's also good for the google archives (future searches) not to mix up
fresh queries within answered threads.

All the best ..

 

[Joshua]

I was wondering if this same ideal will work with data?

 

[Joshua]

Will this work for Alphanumeric as well as numeric?

 

[Max]

Will this work for Alphanumeric as well as numeric?

Yes, I think so. Easiest way is to play with
the sample file provided earlier, viz.:

http://www.savefile.com/files/9916738
Compare MultiCol n List Common n Unique Items.xls

In the sheet: Combined, you could quickly test by just changing the entries
within A2:A10 to numbers, alphas or alphanums, then see whether the correct
results are returned in cols M to P