1

I have a table on sheet 1 as follows:

Name | Type | Number |
---------------------
x    |  1  |  av3    |
---------------------
y    |  2  |  bu4    |
---------------------
a    |  1  |  23r    |
---------------------
b    |  1  |  gk4    |

I need to separate the table into two separate sheets based on type, so type 1 is populated on sheet 2, and type 2 is populated on sheet 3.

Below is how I would like sheet 2 to be displayed:

Name  | x   | 
-------------
Number| av3 |
-------------
      |     |
-------------
Name  |  a  |
-------------
Number| 23r |
-------------
      |     |
-------------
Name  |  b  |
------------
Number| gk4|

Below is how I would like sheet 3 to be displayed:

Name  | y  |
------------
Number| bu4|
------------

I am currently trying to use vlookup to reference the type and populate the data however vlookup will only find one match and will not find a the next match down. How do I get around this?

1

I would suggest a pivot table would be the easiest way to do this.

  1. Select the table, and click Insert then Pivot Table.

  2. Drag Name into the Report Labels box. Then drag Number directly below it.

  3. To add a line between each group, right-click any name value in your pivot table (eg 'x'), and click Field Settings. Go to the Layout & Print tab, tick the Insert blank lines after each item label box and click OK.

  4. Insert a column to the left of your pivot table, then add Name / Number labels as required. If you need this to be dynamic you can use something like =IFERROR(IF(B4="","",IF(MATCH(B4,Sheet1!$A$1:$A$5,0),"Name")),"Number") where B4 is the first row of your pivot table, and Sheet1!A1:A5 is the name column in your original table.

  5. Format (colours etc) as required! You will probably want to hide the expand/collapse buttons, field headers and totals, and left align everything in the pivot table.

  6. Remove any names you don't want to appear on this tab. There are various ways to do this, one is to right click the value (eg 'y'), hover over Filter then click Hide selected items.

  7. Repeat for as many tabs as you want, filtering on Name accordingly.

output

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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