MATCH and INDEX into macro

[nedmt60]

Hello All,

It took me a while to figure out the following formula but it's not quite what I need: {=IFERROR(INDEX($C:$C,MATCH(1,(F$1=$B:$B)*($E2=$A:$A),0)),"")}

Column A is an integer
Column B is an integer
Column C is text (3 options, X,Y,Z)

Data will be added to columns A, B and C at the end of the week.

At the moment, the formula results in the table below but I would like something that would also add the additional results (e.g. 1,0 = XY rather than just X and 3,2 = XZ rather than X))

3 2 X 0 1 2 3 4 5
1 2 Y 0 X
1 0 X 1
4 4 Z 2 Y X
1 0 Y 3
3 2 Z 4 Z
5

The actual table is closer to 3500 x 70 and it takes forever for the formula to process (so long i've never waited for it to finish) so I'm hoping a macro will speed it up.

Thanks for any help.

N

[daffodil11]

You'd probably get a better processing time if you declared your row values. Indexing and matching 3500 rows would take a bit less time than a million.

[nedmt60]

Sounds good. I'm a complete novice at excel though so how would I go about that?

Thanks.

[daffodil11]

Just change the formula to match your range of data. Let's pretend your data was in A1 to C50.

{=IFERROR(INDEX($C1:$C50,MATCH(1,(F$1=$B1:$B50)*($E2=$A1:$A50),0)),"")}

See how I added row numbers to correspond in the formula? If you have 3500 rows and your data started at row 10, you'd be looking at:

{=IFERROR(INDEX($C10:$C3510,MATCH(1,(F$1=$B10:$B3510)*($E2=$A10:$A3510),0)),"")}

It's really just a matter of Selecting the B:B in your formula, and then go into the spreadsheet and highlight the only rows with actual data. Repeat for A:A, B:B, C:C.

That's like 3500*3 = 10500 calculations.

Without that, you're telling Excel to go search every row possible, which is like 1,048,576*3 = 3,145,728. So, a lot.

[nedmt60]

Ok, I understand. That definitely helps with the speed.

The other part I need help with is recognizing 2 (or more) different results and putting them in the correct location.

[daffodil11]

I'm not sure I can help with that, abstractly.

You'll always get the best results on these boards by attaching a file and showing before and after results.

Clicking Go Advanced next to Post Quick reply at the bottom
Scroll to the bottom of the page that loads and click on manage attachments
Browse to your file
Click upload
Click done
Finish typing whatever in the posting box, and then hit submit reply.


If your data is sensitive, then make it unsensitive or create something analagous. Just be sure to show what you have, and what you need.

[nedmt60]

Ok, thanks for your help

[nedmt60]

Hi,

Here is an example with the formula included and what I hope the table to look like.

Thanks,

N

[daffodil11]

G13:

=IFERROR(IF(INDEX($C$2:$C$15,MATCH($G13&H$12,$A$2:$A$15&$B$2:$B$15,0))=LOOKUP(2,1/(($A$2:$A$15=$G13)*($B$2:$B$15=H$12)),$C$2:$C$15),LOOKUP(2,1/(($A$2:$A$15=$G13)*($B$2:$B$15=H$12)),$C$2:$C$15),INDEX($C$2:$C$15,MATCH($G13&H$12,$A$2:$A$15&$B$2:$B$15,0))&LOOKUP(2,1/(($A$2:$A$15=$G13)*($B$2:$B$15=H$12)),$C$2:$C$15)),"")

This is an array formula, confirmed with Ctrl+Shift+Enter

Then copy is over and down as needed.


Might have better luck with a ConcatIfs UDF, but I couldn't find one anywhere that had multiple criteria.

[nedmt60]

I've made a little progress but still not quite there.

Does anyone have any solutions?

Thank you.

[nedmt60]

Sorry, I didn't see the response.

I'll go test that now

[nedmt60]

G13:

=IFERROR(IF(INDEX($C$2:$C$15,MATCH($G13&H$12,$A$2:$A$15&$B$2:$B$15,0))=LOOKUP(2,1/(($A$2:$A$15=$G13)*($B$2:$B$15=H$12)),$C$2:$C$15),LOOKUP(2,1/(($A$2:$A$15=$G13)*($B$2:$B$15=H$12)),$C$2:$C$15),INDEX($C$2:$C$15,MATCH($G13&H$12,$A$2:$A$15&$B$2:$B$15,0))&LOOKUP(2,1/(($A$2:$A$15=$G13)*($B$2:$B$15=H$12)),$C$2:$C$15)),"")

This is an array formula, confirmed with Ctrl+Shift+Enter

Then copy is over and down as needed.


Might have better luck with a ConcatIfs UDF, but I couldn't find one anywhere that had multiple criteria.

Perfect, thank you so much