Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have 3000 rows with 25 columns of data. I would like to group data based on the value in one column. This column has numbers (basically a bill of material). So the data is like 1 2 3 4 4 4 5 4 3 4 etc. Basically these numbers represent level of details to get to a part. in the above example first occurrence of 2 means that it is child of 1 and so on.

I would like to group it in a way so that I can expand on each level. I also want all level to come out with a specific color. For example once I click on the + in front of 1 it should list out all the 2 (with a +sign in front of all 2 if there is further detail in 2 and nothing in front of those 2 if there is no underneath information).

Please help me. If I have confused you enough than let me know, I can try to be more clear.

share|improve this question
are you looking for some sort of a treeview? – user222864 Aug 30 '13 at 7:14
Hello All, I have tried to work with group command and wrote a little script as well. I am still debugging it. Yes, The tree structure is what I need. You described it perfectly. The code I am working is like this – Sandeep Aug 30 '13 at 13:08
Sub Copy_data() currentrow = 2 k = 0 no_rows = 1 Range("A2").Select For i = 1 To 12 Do While ActiveCell.Value <> Empty Do While ActiveCell.Offset(1, 0) > i k = k + 1 ActiveCell.Offset(1, 0).Select Loop If k > 0 Then Range(Cells(currentrow + 1, 1), Cells(currentrow + k, 1)).Select Selection.Rows.Group End If currentrow = currentrow + k + 1 Range(Cells(currentrow, 1), Cells(currentrow, 1)).Select k = 0 Loop currentrow = 2 k = 0 Range("A2").Select Next i End Sub – Sandeep Aug 30 '13 at 13:11
Any suggetions? – Sandeep Sep 3 '13 at 15:39
Got it working finally – Sandeep Sep 4 '13 at 12:14

Try using =LEFT and =MID formula:

Insert a new column after the column containing material code and write this formula in column B: if the first level has 2 digits and the material code column is A s =left(A1,2), fill it to down. Insert a column after it and write this on column c: =MID(A1,3,2), fill it to the down.

Do this for other numbers.

Now you can use data filter to have for example materials with master code of 01 and sub code of 03 and so on.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.