Nesting MATCH functions? Or other solution?

  • Thread starter Thread starter KCobler
  • Start date Start date
K

KCobler

I have a worksheet where I dump exported data from another program. The
worksheet has a main category in column A and subcategories in column B.
(Example below).

I am interested in finding data in the row "Total Contribution" under the
category of "Construction Loan".

Is there a way to ask for a nested MATCH statement on both "Construction
Loan" and "Total Contribution" ? As you can see, the title "Total
Contribution" is repeated in each of the Column A categories, and I just want
the data for the Construction Loan category. Further, the title in Column A
is only at the top of each section, and not repeated in front of the
Subcategories of Column B.

I can see some possible work-arounds, but what would be the best way to
retrieve the data I want? Thanks.


Data example:


Finance : Equity
Timed Contribution: Project
Auto. Project Contribution
Total Contribution
Finance : Construction Loan
Timed Contribution: Project
Auto. Project Contribution
Total Contribution
Finance : Mortgage
Opening Balance
Total Loan Fees
Total Contribution
 
Hi,

Is the data in separate columns or indented, I can't tell from your data
layout?
 
KCobler said:
I have a worksheet where I dump exported data from another program. The
worksheet has a main category in column A and subcategories in column B.
(Example below).

I am interested in finding data in the row "Total Contribution" under the
category of "Construction Loan".

Is there a way to ask for a nested MATCH statement on both "Construction
Loan" and "Total Contribution" ? As you can see, the title "Total
Contribution" is repeated in each of the Column A categories, and I just want
the data for the Construction Loan category. Further, the title in Column A
is only at the top of each section, and not repeated in front of the
Subcategories of Column B.

I can see some possible work-arounds, but what would be the best way to
retrieve the data I want? Thanks.


Data example:


Finance : Equity
Timed Contribution: Project
Auto. Project Contribution
Total Contribution
Finance : Construction Loan
Timed Contribution: Project
Auto. Project Contribution
Total Contribution
Finance : Mortgage
Opening Balance
Total Loan Fees
Total Contribution
Click to expand...

I would usually fill in the missing data. Easiest way I have found is to add an
AutoFilter to column A and select "Blanks". Assuming the first "Blank" cell is
A3, put the formula "=A2" in A3 and hit enter. Then copy A3, select all the
rest of the "Blanks" in column A and then paste. Then remove the AutoFilter.

After the sheet recalculates, you can Copy / Paste Special / Values on column A
to get rid of the formulas. If you want the same look as before, use
conditional formatting on column A to apply white color to the text if it is the
same as the cell above.

From there there are several ways you could get your data. I would suggest a
PivotTable.
 
Back
Top