Database help. From database list to nice looking list

[Trille]

I have a database in Sheet1 that looks something like this:

100 Category, 01 Subcategory, A Item name, Data, Data, Data ...
100 Category, 01 Subcategory, B Item name, Data, Data, Data ...
100 Category, 01 Subcategory, C Item name, Data, Data, Data ...
100 Category, 01 Subcategory, D Item name, Data, Data, Data ...
100 Category, 02 Subcategory, A Item name, Data, Data, Data ...
100 Category, 02 Subcategory, B Item name, Data, Data, Data ...
100 Category, 03 Subcategory, A Item name, Data, Data, Data ...
100 Category, 03 Subcategory, B Item name, Data, Data, Data ...
100 Category, 03 Subcategory, C Item name, Data, Data, Data ...
200 Category, 01 Subcategory, A Item name, Data, Data, Data ...
200 Category, 01 Subcategory, B Item name, Data, Data, Data ...
200 Category, 01 Subcategory, C Item name, Data, Data, Data ...
200 Category, 01 Subcategory, D Item name, Data, Data, Data ...
200 Category, 02 Subcategory, A Item name, Data, Data, Data ...
200 Category, 02 Subcategory, B Item name, Data, Data, Data ...
200 Category, 03 Subcategory, A Item name, Data, Data, Data ...
200 Category, 03 Subcategory, B Item name, Data, Data, Data ...
200 Category, 03 Subcategory, C Item name, Data, Data, Data ...

Commas represent cells. Category, subcategory and items of course have real names.


How do I make a "print out" in Sheet2 that looks like this?:

100 Category
100.01 Subcategory

100.01.A Item name, Data, Data, Data ...
100.01.B Item name, Data, Data, Data ...
100.01.C Item name, Data, Data, Data ...
100.01.D Item name, Data, Data, Data ...

100.02 Subcategory

100.02.A Item name, Data, Data, Data ...
100.02.B Item name, Data, Data, Data ...

100.03 Subcategory

100.03.A Item name, Data, Data, Data ...
100.03.B Item name, Data, Data, Data ...
100.03.C Item name, Data, Data, Data ...

200 Category
200.01 Subcategory

200.01.A Item name, Data, Data, Data ...
200.01.B Item name, Data, Data, Data ...
200.01.C Item name, Data, Data, Data ...
200.01.D Item name, Data, Data, Data ...

200.02 Subcategory

200.02.A Item name, Data, Data, Data ...
200.02.B Item name, Data, Data, Data ...

200.03 Subcategory

200.03.A Item name, Data, Data, Data ...
200.03.B Item name, Data, Data, Data ...
200.03.C Item name, Data, Data, Data ...

Whether items are listed as "100.01.A Item name" or simply ".A Item name" is not important.
The main thing is that I want a nice looking list with titles and subtitles. Preferably with special text formatting applying to the titles and subtitles.

..

[PeteABC123]

Access would probably be your best friend for this effort. Text to columns from your data, coma del., do an import, create your table, and create your form.
Not an easy task.
Pete

[bigroo1958]

How about displaying in a pivot table, looks like this would work.

[azumi]

For 100 and 200 Category, put in A and copied down

=IF(COUNTIF(Sheet1!$A$1:A1,Sheet1!A1)=1,Sheet1!A1,0)

For Subcategory, put this on B2 and copied down
=IF(COUNTIFS(Sheet1!$A$1:A1,Sheet1!A1,Sheet1!$B$1:B1,Sheet1!B1)=1,Sheet1!B1,0)

For Item Name, put this on C2 and array entered and copied down
=INDEX(Sheet1!$C$1:$C$18,SMALL(IF((LOOKUP(ROW(Sheet1!$A$1:$A$18),ROW(Sheet1!$A$1:$A$18)/(Sheet1!$A$1:$A$18<>""),Sheet1!$A$1:$A$18)=LOOKUP(2,1/NOT(ISNUMBER($A$2:A2)),$A$2:A2))*(Sheet1!$B$1:$B$18=LOOKUP(2,1/NOT(ISBLANK($B$2:B2)),$B$2:B2)),ROW(Sheet1!$C$1:$C$18)-ROW(Sheet1!$C$1)+1),H2))

For Data, put this D2 and array entered, then copied down
=IFERROR(INDEX(Sheet1!$D$1:$D$18,SMALL(IF(Sheet1!$C$1:$C$18=C2,ROW(Sheet1!$D$1:$D$18)-ROW(Sheet1!$D$1)+1),COUNTIF($C$2:C2,C2))),"")

for column A and B some cells produces 0 and you better remove it with custom format with this code 0;-0;;@ and copy the format

The last I'm using helper columns, put this on H2 and copy down
=IF(B2<>0;1;H1+1)

CHEEEEEERSSSSS

[PeteABC123]

Awesome work azumi.
Pete

[azumi]

thanks for rep Pete