Grouping of Items based on same Parts in Excel

Dear ExcelForum users

I am working with a large data set containing information about certain items

The items are structured so that they all have seven main sections. Within each main section their is a variable number of parts, that the items are made of.
The items uses only one part from each main section. So all items consists of seven (identical) main sections and seven parts.

I am looking to group items togheter which are precisely identical. I mean the items that uses the same part in all the main sections.

Is there an Excel/math wizard out there who can help me out with this?
I have all the data, but I can't make Excel do this task for me..

Best regards
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
If I understand you correctly, you have 7 columns of data that you want to match exactly. If so, you can concatenate those 7 columns together, then sort on the concatenated value. Items that are identical will be placed together after the sort.

To do the concatenation, you would use a formula like:
=A2 & "|" & B2 & "|" & C2 & "|" & D2 & "|" & E2 & "|" & F2 & "|" & G2
This formula puts a pipe character between each part number. I assume the pipe symbol will never be a character in one of the part numbers, so including it in the concatenation will make certain that characters from one part number won't be confused with characters from another in the concatenated string.

dannyjoerAuthor Commented:
Thanks Brad

It sounds like something I ought to try. I am not sure i fully understand it though. I have tried attaching a sample workbook. So that you can look at the nature of my data. And within is a small description of what I am trying to achieve..

byundtMechanical EngineerCommented:
In your sample workbook, I assumed that you wanted to group all items together that matched in columns B, C & D.

Since you are using Excel 2007 or later, I used a structured concatenation formula on those three columns:
=[@Column1] & "|" & [@[Recom Part]] & "|" & [@Part]
Note the use of the pipe symbol | as a separator between each part number in the concatenation.

I then sorted by the results of that formula. This grouped the items together that had the same parts.

I then numbered the unique combinations with this formula in cell F3:
=([@[Concatenation formula]]<>E2)+F2


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.