PDA

View Full Version : VBA Insert array formula base on criterias



paazan
02-02-2017, 02:48 PM
Description:

Please refer to attached spread sheet

I have the original array formula, which is an array formula, inserted in Sheet1 B4, the formula draws data from sheet2 base on sheet1 column A
And as I copy down, two variables in the formula will be changed: the sheet1 column A reference going A4, A5,A6....
and close to the right end of the formula where it says "1:1" going "2:2", "3:3"....

It's not working properly because when ever the data in Sheet1 column A changes, the formula should be reset back to "1:!"
and I want to do this:
create a macro that inserts the formula in b4 and copy down, as long as the reference in Sheet1 Column A changes, the formula is reset back to "1:1" and copy down the formula, until next change, repeat.

Any help will be appreciated.
Also if it can be done without VBA but require extra column as middle steps would be fine.
18225

p45cal
02-03-2017, 04:43 AM
Nowhere do you state what you're trying to do.
It looks like a version of vlookup; what order are you wanting the result to be in?
Is sheet1 data always going to be sorted?
Is sheet2 data always going to be sorted?

paazan
02-03-2017, 08:44 AM
Thanks a lot for your response, p45cal.

I need to place a formula in sheet1 B4 first and copy it down, hoping it change the metrics in the formula and return matching results from sheet2 column B if a match is found in sheet2 column A.

Here, the "lookup" value is stored in sheet2 column A, which means it is a range.
For example, the formula on Sheet1 B4 would look for the FIRST value in Sheet2 column A that matches sheet1 A4, if found, return the value in Sheet2 column B next to it.
As the formula is copied down and when it works the same way. IF the case that the value in Sheet1 column A is the same is A4, say, A25=A4, both 2819 in this case, and if it is the second 2819, the second match in sheet2 will be returned, but not the first one which vlookup might have done.

In the case that I only need to lookup one particulate value and return a result accordingly, it works perfectly.
But now I need to expand it to a range, and I got stuck.

Sorry I forgot to mention this:
sheet1 and sheet2 are NOT always going to be sorted. In fact they are the result of other steps and chances are every time I run it, the data on sheet1 column A would be different, but the order of sheet1 A:A and sheet2 A:A will be the same. Sheet2 might be a bigger data sheet, but for the ones that I need to lookup for, they are in the same order.
But they will NOT be sorted. otherwise the returns won't be right.

p45cal
02-03-2017, 09:25 AM
In sheet1 cell B4, array-entered:

=INDEX(Sheet2!$B$1:$B$20,SMALL(IF(Sheet2!$A$1:$A$20= A4,ROW(Sheet2!$A$1:$A$20)),COUNTIF($A$4:$A4,$A4)))
Copy down. Is that right?

You'll notice that instead of entire column references I've used specific ranges. This is because using entire columns is a huge resource drain on calculation.
Wherever I've used 20, change it to the max possible row number you'll ever reach. Even if it's 20,000, it is still a much smaller draw on resources than the entire column.

paazan
02-06-2017, 11:32 AM
Thanks a lot.

It works and definitely a better way to do it without necessary to use VBA.

Another question if you don't mind.
Is there a way I can set the name of sheet as variable in this kind of array formula?
Which means the sheet1, sheet2, sheet3 would be reading from a perticular sheet based on some criteria or linked to another sheet in where I can put the sheet name to be used.

p45cal
02-07-2017, 10:04 AM
Yes. Say you have the sheet name in cell F1 of Sheet1, then
=INDEX(INDIRECT("'" & $F$1 & "'!$B$1:$B$20"),SMALL(IF(INDIRECT("'" & $F$1 & "'!$A$1:$A$20")= A4,ROW(INDIRECT("'" & $F$1 & "'!$A$1:$A$20"))),COUNTIF($A$4:$A4,$A4)))
array-entered sad before, should do it.