Retrieve and group row data by multiple critieria

[Guest]

I have a worksheet that will have variable numbers of data row entries, and
variable numbers of columns depending on entries chosen in the first two
columns. I want to group those rows in different sections of another
worksheet, based on the type of entry.

I'm not sure if I'm explaining it very well. This is an expense report
list. I would like the user to be able to enter each expense and have the
second worksheet group by personal or company charge, then sum the costs of
each. The entry worksheet has the following columns:
Col A: Personal or Company Charge - drop-down list, 2 choices
Col B: Type - drop-down list, 5 choices
Col C: Cost
Col D: Label based on type chosen (If "Personal Auto", Col D says "# Miles")
Col E: Data entry (depending on Col D label)
Col F: Label based on type chosen (blank for 1 "type")
Col G: Data entry (depending on Col F label)
Col H: Label based on type chosen (blank for 3 "types")
Col I: Data entry (depending on Col H label)

On the second worksheet, I want to list the personal charge rows by type in
one area, and the company charges by type in a different area. I thought I
had it for a minute, using nesting IF statements, but I don't know how to say
"get the next one if the first one has already been listed". Is this doable,
or am I going about this the wrong way?

Thanks for any help or suggestions!

 

[S Davis]

....

not sure... what is going on... ...... ( )

but, couldnt you simply say, =if(a1<>"",sheet1!b1,"") in b1? (ie, if a1
is blank do nothing, but if it has a value in it then retrieve the
information from sheet1)

 

[S Davis]

....

not sure... what is going on... ...... ( )

but, couldnt you simply say, =if(a1<>"",sheet1!b1,"") in b1? (ie, if a1
is blank do nothing, but if it has a value in it then retrieve the
information from sheet1)

 

[Guest]

Hi S - thanks for answering! It's not really quite that simple -

Maybe giving some data will make more sense. Let's say the data in the
first worksheet only has 6 lines, with the following info:

A1ersonal or Company Charge B1:Type C1:Cost
A2ersonal B2:Meals C2:$41.00 D2:B,L,D
E2:L F2:# of Diners G2: 2 H2:Notes I2:Meet client at airport
A3:Company Charge B3:Transportation C3:$50.00 D3:Item E3:Taxi
A4ersonal B4ersonal Auto C4:$4.45 D4:# Miles E4:10 F4:From
G4:home H4:To I4:airport
A5ersonal B5:Miscellaneous C5:$5.00 D5:Item E5:tip baggage carrier
A6:Company Charge B6:Lodging C6:$1,796.00 D6:Location E6:SAN
F6ates G6: 7/23-7/28/2006
A7ersonal B7:Meals C7:$12.00 D7:B,L,D E7:B F7:# of Diners G7:1
H7:Notes I7:Coffee & muffin

On the second worksheet, what I'm trying to do is group all of the Personal
charges by "type", then group all of the Company charges by "type". In the
above example, in one area I would have Row 2 and Row 7 listed first
(Personal - Meal), then Row 4 and Row 5 (Personal - Personal Auto, Personal -
Miscellaneous), then in a separate area list Row 3 and then Row 6 (Company
Charge - Transportation, Company Charge - Lodging).

In the second sheet, if you assume the first cell is A8, this is what I had
in A8:

=IF(Sheet1!A2="Personal",Sheet1!A2,IF(Sheet1!A3="Personal",Sheet1!A3,IF(Sheet1!A4="Personal",Sheet1!A4,IF(Sheet1!A5="Personal",Sheet1!A5,IF(Sheet1!A6="Personal",Sheet1!A6,IF(Sheet1!A7="Personal",Sheet1!A7,IF(Sheet1!A8="Personal",Sheet1!A8,IF(Sheet1!A9="Personal",Sheet1!A9, ""))))))))

Then in A9:
=IF(A8=Sheet1!A2,Sheet1!B2,IF(A8=Sheet1!A3,Sheet1!B3,IF(A8=Sheet1!A4,Sheet1!B4,IF(A8=Sheet1!A5,Sheet1!B5,IF(A8=Sheet1!A6,Sheet1!B6,IF(A8=Sheet1!A7,Sheet1!B7,IF(A8=Sheet1!A8,Sheet1!B8,IF(A8=Sheet1!A9,Sheet1!B9, ""))))))))

In A10: =IF(AND(A8=Sheet1!A2,
B8=Sheet1!B2),Sheet1!C2,IF(B8=Sheet1!B3,Sheet1!C3,IF(B8=Sheet1!B4,Sheet1!C4,IF(B8=Sheet1!B5,Sheet1!C5,IF(B8=Sheet1!B6,Sheet1!C6,IF(B8=Sheet1!B7,Sheet1!C7,IF(B8=Sheet1!B8,Sheet1!C8,IF(B8=Sheet1!B9,Sheet1!C9, ""))))))))

But obviously, the deeper I got the more wrong this approach is! If I enter
two personal meal types entries in a row, I get them listed over and over
again. Not to mention I need many more row possibilities than it will let me
enter.

I'm trying to figure out whether to use the INDEX, MATCH, or one of the
LOOKUP functions. I've tried taking examples from previous posts, but I
haven't found one that matches the situation exactly, and I'm not sure any
are the correct command for this situation. I could do a lot better with SQL
or CR, but I'm not very familiar with the intricacies of Excel.

Basically, I want to return unique rowsets where column A = Personal,
grouped by Personal, then Type. It doesn't have to search unlimited amounts
of rows, but at least 50. And I don't want blank rows between the types
(which I would get if I just had a direct 'retreive this value if =
"personal"')

I hope I'm not confusing it more.

Thanks again

 

[Guest]

Hi S - thanks for answering! It's not really quite that simple -

Maybe giving some data will make more sense. Let's say the data in the
first worksheet only has 6 lines, with the following info:

A1ersonal or Company Charge B1:Type C1:Cost
A2ersonal B2:Meals C2:$41.00 D2:B,L,D
E2:L F2:# of Diners G2: 2 H2:Notes I2:Meet client at airport
A3:Company Charge B3:Transportation C3:$50.00 D3:Item E3:Taxi
A4ersonal B4ersonal Auto C4:$4.45 D4:# Miles E4:10 F4:From
G4:home H4:To I4:airport
A5ersonal B5:Miscellaneous C5:$5.00 D5:Item E5:tip baggage carrier
A6:Company Charge B6:Lodging C6:$1,796.00 D6:Location E6:SAN
F6ates G6: 7/23-7/28/2006
A7ersonal B7:Meals C7:$12.00 D7:B,L,D E7:B F7:# of Diners G7:1
H7:Notes I7:Coffee & muffin

On the second worksheet, what I'm trying to do is group all of the Personal
charges by "type", then group all of the Company charges by "type". In the
above example, in one area I would have Row 2 and Row 7 listed first
(Personal - Meal), then Row 4 and Row 5 (Personal - Personal Auto, Personal -
Miscellaneous), then in a separate area list Row 3 and then Row 6 (Company
Charge - Transportation, Company Charge - Lodging).

In the second sheet, if you assume the first cell is A8, this is what I had
in A8:

=IF(Sheet1!A2="Personal",Sheet1!A2,IF(Sheet1!A3="Personal",Sheet1!A3,IF(Sheet1!A4="Personal",Sheet1!A4,IF(Sheet1!A5="Personal",Sheet1!A5,IF(Sheet1!A6="Personal",Sheet1!A6,IF(Sheet1!A7="Personal",Sheet1!A7,IF(Sheet1!A8="Personal",Sheet1!A8,IF(Sheet1!A9="Personal",Sheet1!A9, ""))))))))

Then in A9:
=IF(A8=Sheet1!A2,Sheet1!B2,IF(A8=Sheet1!A3,Sheet1!B3,IF(A8=Sheet1!A4,Sheet1!B4,IF(A8=Sheet1!A5,Sheet1!B5,IF(A8=Sheet1!A6,Sheet1!B6,IF(A8=Sheet1!A7,Sheet1!B7,IF(A8=Sheet1!A8,Sheet1!B8,IF(A8=Sheet1!A9,Sheet1!B9, ""))))))))

In A10: =IF(AND(A8=Sheet1!A2,
B8=Sheet1!B2),Sheet1!C2,IF(B8=Sheet1!B3,Sheet1!C3,IF(B8=Sheet1!B4,Sheet1!C4,IF(B8=Sheet1!B5,Sheet1!C5,IF(B8=Sheet1!B6,Sheet1!C6,IF(B8=Sheet1!B7,Sheet1!C7,IF(B8=Sheet1!B8,Sheet1!C8,IF(B8=Sheet1!B9,Sheet1!C9, ""))))))))

But obviously, the deeper I got the more wrong this approach is! If I enter
two personal meal types entries in a row, I get them listed over and over
again. Not to mention I need many more row possibilities than it will let me
enter.

I'm trying to figure out whether to use the INDEX, MATCH, or one of the
LOOKUP functions. I've tried taking examples from previous posts, but I
haven't found one that matches the situation exactly, and I'm not sure any
are the correct command for this situation. I could do a lot better with SQL
or CR, but I'm not very familiar with the intricacies of Excel.

Basically, I want to return unique rowsets where column A = Personal,
grouped by Personal, then Type. It doesn't have to search unlimited amounts
of rows, but at least 50. And I don't want blank rows between the types
(which I would get if I just had a direct 'retreive this value if =
"personal"')

I hope I'm not confusing it more.

Thanks again