Quicker way to process more than one row using Index Match

[Padthelad]

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

ID	Name	Detail	Date
1A	Joe	Arm procedure	24-Mar​
2A	David	Leg procedure	12-Feb​
3A	Sarah	Shoulder injection	01-Apr​
1B	Hannah	Knee Pain	03-Feb​
2B	Steve	Pain	05-Feb​
3B	Claire	Shoulder injection	06-Feb​
1A	Patrick	Left Knee	24-Mar​
1A	Robert	Shoulder injection	03-Feb​
2A	William	Left Knee	05-Feb​
3A	Peter	Arm procedure	06-Feb​
1B	James	Leg procedure	24-Mar​
2B	Harry	Shoulder injection	24-Mar​
3B	Emma	Knee Pain	12-Feb​
1A	Jenifer	Pain	01-Apr​
2B	Lucy	Shoulder injection	03-Feb​
3B	Janet	Left 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).

		ID	1A
ID	Name	Detail	Date
1A	Joe	Arm procedure	24/03/2020​
1A	Patrick	Left Knee	24/03/2020​
1A	Robert	Shoulder injection	03/02/2020​
1A	Jenifer	Pain	01/04/2020​

I am using the below array formula to index/match based on the value in D1 (1A):-

{=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.

 

[tyija1995]

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]

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]

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]

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]

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]

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]

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
	A	B	C	D	E	F	G	H	I
1	ID	Name	Detail	1A		1A	Joe	Arm procedure	24-Mar
2	1A	Joe	Arm procedure	24-Mar		1A	Patrick	Left Knee	24-Mar
3	2A	David	Leg procedure	12-Feb		1A	Robert	Shoulder injection	03-Feb
4	3A	Sarah	Shoulder injection	01-Apr		1A	Jenifer	Pain	01-Apr
5	1B	Hannah	Knee Pain	03-Feb
6	2B	Steve	Pain	05-Feb
7	3B	Claire	Shoulder injection	06-Feb
8	1A	Patrick	Left Knee	24-Mar
9	1A	Robert	Shoulder injection	03-Feb
10	2A	William	Left Knee	05-Feb
11	3A	Peter	Arm procedure	06-Feb
12	1B	James	Leg procedure	24-Mar
13	2B	Harry	Shoulder injection	24-Mar
14	3B	Emma	Knee Pain	12-Feb
15	1A	Jenifer	Pain	01-Apr
16	2B	Lucy	Shoulder injection	03-Feb
17	3B	Janet	Left Knee	43883
Base Data

Cell Formulas
Range	Formula
F1:I4	F1	=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

 

[Fluff]

@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]

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.