Quicker way to process more than one row using Index Match

Padthelad

Board Regular
Joined
May 13, 2016
Messages
64
Office Version
  1. 2016
Platform
  1. 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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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.
 
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
@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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top