Quicker way to process more than one row using Index Match

Padthelad

Board Regular
Joined
May 13, 2016
Messages
64
Office Version
2016
Platform
Windows
Hi,

I have the below base data that I want to split in to individual sheets. So that each ID has its own sheet. ID heading is in cell A1

IDNameDetailDate
1AJoeArm procedure
24-Mar​
2ADavidLeg procedure
12-Feb​
3ASarahShoulder injection
01-Apr​
1BHannahKnee Pain
03-Feb​
2BStevePain
05-Feb​
3BClaireShoulder injection
06-Feb​
1APatrickLeft Knee
24-Mar​
1ARobertShoulder injection
03-Feb​
2AWilliamLeft Knee
05-Feb​
3APeterArm procedure
06-Feb​
1BJamesLeg procedure
24-Mar​
2BHarryShoulder injection
24-Mar​
3BEmmaKnee Pain
12-Feb​
1AJeniferPain
01-Apr​
2BLucyShoulder injection
03-Feb​
3BJanetLeft Knee
22-Feb​

The outcome I require is a tab called 1A as below. The headings for the table start in row 4 (ID heading is in cell A4).

ID1A
IDNameDetailDate
1AJoeArm procedure
24/03/2020​
1APatrickLeft Knee
24/03/2020​
1ARobertShoulder injection
03/02/2020​
1AJeniferPain
01/04/2020​

I am using the below array formula to index/match based on the value in D1 (1A):-
Rich (BB code):
{=IFERROR(INDEX('Base Data'!A:A,SMALL(IF('Base Data'!$A:$A=$D$1,ROW('Base Data'!$B:$B)),ROW(1:1))),"")}
This formula works by looking for the value in cell D1 (1A) and then giving the first line in that base data, then the second and so on.

This formula works well for a small data set, but becomes very slow when applied to a larger data set.

Do any of you Excel wizards out there know of a more efficient way to speed up the processing of this formulas so that it works more smoothly and faster? It currently takes over 1.5 hours to process 4000 lines of base data in to approx. 30 tabs.

Any suggestions gratefully received.

Thank you.
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
766
Office Version
365
Platform
Windows
Hey, I don't suppose you have O365 with the FILTER function do you?

If not, I'm not sure if an INDEX AGGREGATE match would be quicker than a SMALL IF array formula, potentially so as it's not an array formula though.
 

Padthelad

Board Regular
Joined
May 13, 2016
Messages
64
Office Version
2016
Platform
Windows
Hey, I don't suppose you have O365 with the FILTER function do you?

If not, I'm not sure if an INDEX AGGREGATE match would be quicker than a SMALL IF array formula, potentially so as it's not an array formula though.
Hi Tyija,

Thank you for your response.

Filter is not appropriate as I require the data to be entered to an individual sheet and saved for reference in the future. Plus the individual data by ID is to be sent to the ID stakeholders.

How would the aggregate work?
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
766
Office Version
365
Platform
Windows
Hey,

Your formula
{=IFERROR(INDEX('Base Data'!A:A,SMALL(IF('Base Data'!$A:$A=$D$1,ROW('Base Data'!$B:$B)),ROW(1:1))),"")}

In terms of INDEX AGGREGATE would become::
=IFERROR(INDEX('Base Data'!A:A,AGGREGATE(15,6,('Base Data'!$A:$A=$D$1)/('Base Data'!$A:$A=$D$1)*ROW($A:$A),ROWS($A$1:A1))),"")

However as the entire column is being scanned (A:A) it might be worth putting the data in to a table and using table referencing instead of the entire column (2^20) rows!
 

Padthelad

Board Regular
Joined
May 13, 2016
Messages
64
Office Version
2016
Platform
Windows
Hi Tyija,

Thank you for coming back to me so quickly.

The aggregate formula works well, but it doesn't speed up the processing time.

Any other ideas?

Many thanks.
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
766
Office Version
365
Platform
Windows
There are a couple of things I can think of.

1) As I said at the end of my last post, the entire column is being scanned, reduce this by using a table reference if possible, otherwise think about just putting in a manual range e.g. $A$1:$A$1000

2) Instead of IFERROR, perhaps use an IF statement to capture errors.

=IF(COUNTIF($A$1:$A$17,$D$1)>=ROWS($A$1:A1),INDEX('Base Data'!A$1:A$17,AGGREGATE(15,6,('Base Data'!$A$1:$A$17=$D$1)/('Base Data'!$A$1:$A$17=$D$1)*ROW($A$1:$A$17),ROWS($A$1:A1))),"")

This will firstly calculate how many $D$1 occurs in the A range, if it is greater than or equal to the k value in the SMALL part of AGGREGATE, it will give the solution, otherwise a "" so I believe it is quicker than IFERROR in this case.
 

Padthelad

Board Regular
Joined
May 13, 2016
Messages
64
Office Version
2016
Platform
Windows
Thanks. I will try.

This formula doesn't seem to drag across for columns B C and D. I can't seem to figure out which elements of the formula to fix to make this possible?
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
766
Office Version
365
Platform
Windows
It should work when dragged across columns, that's odd.

The part you would focus on changing is the first parameter of the INDEX function, as that is the returned array, in the formula above it is A$1:A$17, so it is relative to the columns spanned so that should alter to B, C, D as dragged?

My setup:

NCT_Mapping_Doc.xlsx
ABCDEFGHI
1IDNameDetail1A1AJoeArm procedure24-Mar
21AJoeArm procedure24-Mar1APatrickLeft Knee24-Mar
32ADavidLeg procedure12-Feb1ARobertShoulder injection03-Feb
43ASarahShoulder injection01-Apr1AJeniferPain01-Apr
51BHannahKnee Pain03-Feb
62BStevePain05-Feb
73BClaireShoulder injection06-Feb
81APatrickLeft Knee24-Mar
91ARobertShoulder injection03-Feb
102AWilliamLeft Knee05-Feb
113APeterArm procedure06-Feb
121BJamesLeg procedure24-Mar
132BHarryShoulder injection24-Mar
143BEmmaKnee Pain12-Feb
151AJeniferPain01-Apr
162BLucyShoulder injection03-Feb
173BJanetLeft Knee43883
Base Data
Cell Formulas
RangeFormula
F1:I4F1=IF(COUNTIF($A$1:$A$17,$D$1)>=ROWS($A$1:A1),INDEX('Base Data'!A$1:A$17,AGGREGATE(15,6,('Base Data'!$A$1:$A$17=$D$1)/('Base Data'!$A$1:$A$17=$D$1)*ROW($A$1:$A$17),ROWS($A$1:A1))),"")


NB: I improvised with cell $D$1 being 1A :p
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,786
Office Version
365
Platform
Windows
@Padthelad
Two things
1) what version of Excel are you using? You can update your account setting to show this in your mini-profile, which saves members constantly asking. :)
2) Are you happy for this to be done with a macro?
 

Padthelad

Board Regular
Joined
May 13, 2016
Messages
64
Office Version
2016
Platform
Windows
Tyija, I get a circle ref message when I drag across.....? I will have a play and figure it out, thank you.

Fluff - I am on Excel 2016 and have updated my profile.
I would be happy for this to be competed with a macro.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,546
Messages
5,487,493
Members
407,604
Latest member
jortronm

This Week's Hot Topics

Top