programmatic vlookups?

[miconian]

I need to extract a series of values from a worksheet, and the position of the location of the values may vary by worksheet.

So the worksheet looks like this:

FULL-YEAR          REVENUE

GOOGLE             $100
MR.EXCEL           $20
OZGRID              $35

Q1
GOOGLE             $2
MR.EXCEL           $238
OZGRID              $23

Q2
GOOGLE             $98
MR.EXCEL           $93
OZGRID              $91

...and so on for Q3 and Q4.

So, let's say I start with Google. I want to find the Google revenue for Full Year, Q1, Q2, Q3, and Q4. And since I have to do this hundreds of times, I want to do it programatically.

I'm going to be looping through multiple workbooks, and the Google Q2 revenue data is not always in the exact same cell. Basically, I want a function that returns the applicable values, something like this:

Function Gather_Revenue_By_Site (Site As String, wkbk As Workbook)

And it would be run something like this:

Dim GoogleValues[4] As String
Set GoogleValues[4] = Gather_Revenue_By_Site (Google, CurrentWorkbook)

And it would return an array like this:

{$100, $2, $98, $92, $23}

...reflecting whatever the Google data is in the workbook in question.

I'm not sure whether to approach this by:

...doing a programmatic VLOOKUP?
...programmatically finding the addresses of Q1, Q2, etc. and then using those locations as a boundary for some kind of search, VLOOKUP or otherwise?
...something else?

I'll be looping through hundreds of workbooks to do this. The core problem (to me) is that, while the general organization of the worksheet is always the same, the order of the sites is not always the same, and the start and end of each quarterly section are not always in the same place.

I have a general (though not perfect) idea of how to loop through the files and copy data (also explored somewhat in other threads), but what I'm concerned with here is how to parse a particular worksheet to be sure that I'm getting the appropriate revenue number per website per quarter.

Open to providing more information; just trying to keep it simple so my question will be easier to answer. Thanks.

 

[Norie]

I can't see how you could use a lookup as you only appear to have one column of data.

Have you considered reorganising the data to make it easier to work with?

You can do that with code and if the workbooks do have the same layout you can use the same code on each workbook.

 

[Rick Rothstein]

Maybe I am missing something, but wouldn't putting this formula in B3 and copying it down to B5 give you what you want?

=SUMIF(A$7:A$999,A3,B$7:B$999)

Simply adjust the upper row limit to, at minimum, incompass all your data.

 

[miconian]

There are two columns of data. One column contains the website names and the quarters, the other column contains the revenue. I'm not sure how it can seem otherwise, given the way I displayed it above. And actually, that layout was an oversimplification; there are many other types of revenue columns further to the right.

 

[miconian]

Rick, I have to do this with code. There are hundreds of workbooks, and the end goal is to move the data elsewhere, collecting, for example, all the Google data from each workbook, in one place.

 

[Rick Rothstein]

Rick, I have to do this with code. There are hundreds of workbooks, and the end goal is to move the data elsewhere, collecting, for example, all the Google data from each workbook, in one place.

Maybe this code does what you want...

Sub SumQuarters()
  Dim LastRow As Long, FirstQ1Row As Long
  FirstQ1Row = Columns("A").Find("Q1", LookAt:=xlWhole, SearchDirection:=xlNext).Row
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  With Range("B3:B" & FirstQ1Row - 2)
    .FormulaR1C1 = "=SUMIF(R" & FirstQ1Row & "C[-1]:R" & LastRow & "C[-1],RC1,R" & FirstQ1Row & "C:R" & LastRow & "C)"
    .Value = .Value
  End With
End Sub

 

[Norie]

Oops.

Still think the data could do with a bit of work.

 

[miconian]

Thanks, Rick. But there is no summing happening. I need the data as it is, just isolated from everything else on the workbook.

Norie, the data is very complicated. I dread the idea of rearranging it.

 

[Rick Rothstein]

Thanks, Rick. But there is no summing happening. I need the data as it is, just isolated from everything else on the workbook.

I think I am not understanding exactly how your data is laid out or, more importantly, what you want as a result of running the macro. Perhaps you can show us a sample of actual data and what that data should look like after the macro (and where the output should go to).