Creating a summary list from source data - can you??

[Guest]

Don't know if or how one would do this but I thought I would ask for
suggestions...

In worksheet1 have a a list of expenses. col a= date col b= description col
c= amount and col d = expense category (drop down list). Each row is a
seperate expense. There are about 250 in total.

It would be great if in worksheet2 I could list each expense category and
then under each list all expenses that match that category and then lower in
the sheet do the next and the next... etc.

Any help would be greatly appreciated.
Thanks, Marty

 

[Biff]

Hi!

Assume:

Sheet1 A11 are the column headers: Date, Description, Amount, Category

Data starts in A2 on down.

In Sheet2 A11 are the column headers: Cat1, Date, Description, Amount

In Sheet2 B2 enter this formula using the key combo of CTRL,SHIFT,ENTER:

=IF(ROWS($1:1)<=COUNTIF(Sheet1!$D$2:$D$20,$A$1),INDEX(Sheet1!A$2:A$20,SMALL(IF(Sheet1!$D$2:$D$20=$A$1,(ROW(Sheet1!$A$2:$A$20)-ROW(Sheet1!A$2))+1),ROW(1:1))),"")

Copy across to D2 then down until you get blanks (meaning the data has been
exhausted).

Format column B as DATE.

Cat1 is just the name of a category I used for example. Change Cat1 to
whatever your real values are.

Adjust references to suit.

Biff