1

In Excel 2010 (or 2007 - I have both, although my OS is only Win7 32 bit as a limitation for some legacy applications we run), I need to find how I can find and return the matching value from two data arrays.

I have two spreadsheets. One is a giant flat file from a hierarchal OLAP Cube dimension (37,000 rows from SAP BPC). The other is a table of values that I need to match against. I need to return the matching value from the 2nd spreadsheet into ColumnA in the first Sheet -- The flatfile.

The challenge is that since it's a hierarchal structure, I can't select a single column from Sheet1 to match upon -- the match could be in any of the columns of each row. So, basically, I'm looking at needing to take whatever it is that matches between the Sheet 1 single row as an array and the Sheet 2 column as an array (I think).

In English, I want Excel to: For each row of Sheet1 where there's data, look at everything throughout the row (say, range B2:R2 - I left Col A as blank for the formula/match value). If anything there matches anything in the Reporting Category list (which is Sheet 2 column A, Range A1:A42), then return the Sheet2 value to Sheet1!A2 (the blank column I made for the match).

Here's a sample of data with a food allegory. Note that I've made a blank ColumnA, and that the data in each row progresses up a hierarchy of classifications where ColB is the base level and it's repeated if necessary in order for the terminal parent to be in ColF.:

enter image description here

Now, in this next image is the report format that I want to use. See, sometimes we want data from some of the hierarchical levels and sometimes from others.

enter image description here

In the end, my spreadsheet will fill with the customized report categories that I want (then I can pivot on those categories for aggregate data).enter image description here

I've been accomplishing this via monster vlookup formula, but was wondering if there's another, easier or at least less resource-intensive way since 37,000 rows with a vlookup statement nested 8 deep makes Excel like to crash a lot. So, using my real reporting categories (sheet2 is called All_Budget_Units), here's what I currently use:

=IFERROR(VLOOKUP(IFERROR(VLOOKUP(IFERROR(VLOOKUP(IFERROR(VLOOKUP(IFERROR(VLOOKUP(IFERROR(VLOOKUP(C2,All_Budget_Units!$A$1:$A$39,1,FALSE),D2),All_Budget_Units!$A$1:$A$39,1,FALSE),E2),All_Budget_Units!$A$1:$A$39,1,FALSE),F2),All_Budget_Units!$A$1:$A$39,1,FALSE),G2),All_Budget_Units!$A$1:$A$39,1,FALSE),H2),All_Budget_Units!$A$1:$A$39,1,FALSE),I2)

0

2 Answers 2

0

YMMV, but View -> Macros, add a macro. Try this (change the cell references as necessary):

Dim data, reference As Range

Set reference = Worksheets("Sheet2").Range("A1", "A42")
Set data = Worksheets("Sheet1").Range("B2", "F6")

For Each dataCell In data
    For Each referenceCell In reference
        If dataCell.Value = referenceCell.Value Then
                Worksheets("Sheet1").Cells(dataCell.Row, 1).Value = dataCell.Value
        End If
    Next
Next

[Edit: If this works, you can speed it up a bit by stopping searching a row when you've found a match. (Assuming only one possible match per row). e.g.:

Sub newtest()
    Dim data, reference As Range
    Dim skipsome As Boolean
    skipsome = False

    Set reference = Worksheets("Sheet2").Range("A1", "A7")

    Set data = Worksheets("Sheet1").Range("B2", "F6")
    For Each dataCell In data
        For Each referenceCell In reference
            If dataCell.Value = referenceCell.Value Then
                    Worksheets("Sheet1").Cells(dataCell.Row, 1).Value = dataCell.Value
                    skipsome = True
                    Exit For
            End If

            If skipsome = True Then
                skipsome = False
                Exit For
            End If
        Next
    Next
End Sub

Just on your 5 row test data this knocks the cell comparison tests down from 175 to 132.] [Edit2: Make the code work]

1
  • Yes, as it's hierarchal, there is only one possible match per row; otherwise, we'd be duplicating the data by reporting on more than one level of the hierarchy. Thank you for the macro - I'll give it a try!
    – ppfooie
    May 1, 2013 at 17:10
4

You don't need a macro solution for this; you can use an array formula.

{=INDEX(All_Budget_Units!$A$1:$A$39, MAX(IFERROR(MATCH(C2:I2, All_Budget_Units!$A$1:$A$39, 0), 0)))}

This is assuming that the value in I2 is also in the master list, which your original formula doesn't assume. If it is not, or may not be present, use this instead:

{=IFERROR(INDEX(All_Budget_Units!$A$1:$A$39, MAX(IFERROR(MATCH(C2:H2, All_Budget_Units!$A$1:$A$39, 0), -1))), I2)}

If you haven't used array formulas before, you don't type in the braces {} yourself: put in the rest of the formula, and press CTRL+SHIFT+ENTER to enter it as an array formula. If you've done it correctly, the braces will appear in the formula box.

The way this works is that we use the MATCH function to create an array of match results. All but one of these will be #N/A so we wrap it with IFERROR to convert these to 0 (or to -1 in the second version). Any actual match will be a positive number, so taking the MAX of the array finds the single match. Then we use the INDEX function to convert this into the value. In the second version, if there are no matches at all we'll try to use INDEX with a position of -1, which will generate an error, so we use IFERROR to return the default value.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .