Sort and Count Unique Data in 4 Columns

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

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.

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

Please Login or Register  to view this content.