Sort and Count Unique Data in 4 Columns

**miakayuuki** · 05-07-2008, 11:16 AM

Ok,

Here is one I am having some issues trying to figgure out. I need to make this work for my dad, so the more it can be automated, the better as I want to work easily for him.

I have a spreadsheet, and in 4 of the columns in that spreadsheet are part numbers. There is no defined list of part numbers and the ones used can change from time to time. In this case each row defines a cable that is being designed. Column 1 defines the connector type, Column 2 is the cover type on one end, Column 3 is the cover on the other end, and Column 4 is the connector on the other end. Each cell defines a part number that counts as a quantity of 1 that needs to be ordered.

What I am looking for is firstly a way to extract the unique part numbers from those 4 columns, with the end result being the ability to then go back and count how many of each are required. If I had a list of the part numbers defined, I would think that the COUNTIF function would work well here, but with it spread over 4 columns and the fact that I have no idea what the part numbers are ahead of time, I am a little lost.

The ideal outcome here would be to have a separate sheet that would simply have a list (column A) of the unique part numbers, and the quantity of each one that needs to be ordered (column B).

I hope this makes sense, and my apologies if I confused anyone. Thanks for any help you can provide, it would be greatly appreciated.

Mia

**Richard Buttrey** · 05-07-2008, 08:01 PM

Mia[/QUOTE]

Hi, and welcome to the forum.

It's a little difficult without seeing the workbook, but if I've understood correctly, the simplest way would be to use a helper column and concatenate the text/values in the other 4 columns. Then you'll have a unique reference which you can then analyse further with Data-->Filter..>Advanced filter Unique.

HTH. If not post back.

**miakayuuki** · 05-08-2008, 09:24 AM

I think you are on the right track. Basicly my data looks like this

A         B         C         D         E
--------- --------- --------- --------- ---------
Date     P1       P1Bev    P2Bev  P2
1/1/01   BN171   AV121    AV121   CC311
1/2/01   BN171   BC178    AV121   BN171
1/3/01   AA111   BC178    BC178   CC311


Then the end result I want is something like

BN171     3
AA111     1
AV121     3
BC178     3
CC311     2

I know enough to get the list I want if only this was a single column. Tho I do not know how to merge the columns B,C,D,E into a single column which I could then work with using various methods. You make mention of a helper column which sounds promising, but I do not know how to make one. Also note that the first row of each column is a label for the column and should preferably be ignored in the process.

Hope this helps lay it out a little bit better.

Mia

**Richard Buttrey** · 05-08-2008, 11:22 AM

Hi Mia,

It's probably easier with a simple macro. I'm attaching your example data. Just press the green button which runs the macro below to produce your list. You can add new data in the same positions in columns A:E and extract another parts list by pressing the button again - it clears the original list before working out a new unique list and quantity.

HTH

Sub UniqueParts()
Application.ScreenUpdating = False
[G:I].Clear: [G1] = "Connector": [H1] = "Connector": [J1] = "Cover"

Range([b2], [B65536].End(xlUp)).Copy Destination:=[g2]
Range([e2], [e65536].End(xlUp)).Copy Destination:=[g65536].End(xlUp).Offset(1, 0)
[G1].CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=[H1], Unique:=True
[i2] = "=countif(G:G,h2)": [i2].Copy Destination:=Range([i2], [h65536].End(xlUp).Offset(0, 1))
[I:I].Copy: [I:I].PasteSpecial (xlPasteValues)

[G:G].Clear: [G1] = "Cover"
Range([c2], [c65536].End(xlUp)).Copy Destination:=[g2]
Range([d2], [d65536].End(xlUp)).Copy Destination:=[g65536].End(xlUp).Offset(1, 0)
[G1].CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=[J1], Unique:=True
[K2] = "=countif(G:G,J2)": [K2].Copy Destination:=Range([K2], [J65536].End(xlUp).Offset(0, 1))
[K:K].Copy: [K:K].PasteSpecial (xlPasteValues)
[G:G].Clear
[b2].Select
End Sub