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?
byundtConnect With a Mentor Commented:
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

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..

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.