Reply to thread

Message: [QUOTE="KGosh, post: 3664080"] 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: A1:Personal or Company Charge B1:Type C1:Cost A2:Personal 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 A4:Personal B4:Personal Auto C4:$4.45 D4:# Miles E4:10 F4:From G4:home H4:To I4:airport A5:Personal B5:Miscellaneous C5:$5.00 D5:Item E5:tip baggage carrier A6:Company Charge B6:Lodging C6:$1,796.00 D6:Location E6:SAN F6:Dates G6: 7/23-7/28/2006 A7:Personal 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 [/QUOTE]

Verification