Creating easy to read groupings form an excel table

[Glayva]

I have a lengthy spreadhseet which lists 'items' in column A and the 'group' they belong to in column B (see attached example).

I would like to collate this data into simple lists / cards for a presentation in a similar format to that in the attached example (I just need to be able to clearly show the group title and the items which sit within that group).

The output doesn't have to be in Excel, but I am pretty much limited to Microsoft software and a few Adobe products on my work machine.

Does anyone know if there is a quick / automated way of doing this?

Thanks

Glayva

[tigeravatar]

Glayva,

In Excel, you can use a pivot table to get what you're looking for. See attached.

[ChemistB]

See the attachment.

First I created a dummy column that tracked how many of each catagory (i assumed they might not always be in order. You can hide this column if you like. The formula there is

=LEFT(B3,3)&COUNTIF($B$3:$B3,B3)

Then on Sheet2 I formatted without gridlines and with borders to make nice little boxes. I put your catagories in the first row of each box and used this formula dragged across and down

=IF(COUNTIF(Sheet1!$C$3:$C$14,LEFT(Sheet2!A$1,3)&"*")>=ROW(A1), INDEX(Sheet1!$A$3:$A$14,MATCH(LEFT(A$1,3)&ROW(A1),Sheet1!$C$3:$C$14,0)),"")
Is that what you wanted?

EDIT: Of course, Tiger's way is much easier.

[Glayva]

Thanks guys Two good options here.

(Pivot tables have flumoxed me since I moved to the new version of Excel...)