# Quicker way to process more than one row using Index Match

##### Board Regular
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):-
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.

Thank you.

#### Fluff

##### MrExcel MVP, Moderator
Ok, do the sheets need to be created or do they already exist?
If they already exists, is there data on them & if so should that be kept or removed?

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

##### Board Regular
Currently the sheets already exist. They are wiped each month and then repopulated with the base data for the month.

I would be happy for new sheets to be created and populated with the data.
If we repopulate the sheets with new data then the old data would need to be overwritten.

I am happy to use whichever process is easiest.

Many thanks,

#### Fluff

##### MrExcel MVP, Moderator
Rich (BB code):
Dim Cl As Range
Dim Dic As Object
Dim Ws As Worksheet

Set Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = 1
With Sheets("Input")
For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
If Not Dic.Exists(Cl.Value) Then
If Evaluate("isref('" & Cl.Value & "'!A1)") Then
Set Ws = Sheets(Cl.Value)
Ws.Cells.Clear
Else
Set Ws = Sheets(Cl.Value)
End If
.Range("A1").AutoFilter 1, Cl.Value
.AutoFilter.Range.Copy Ws.Range("A1")
End If
Next Cl
End With
End Sub
Change sheet name in red to suit.

1,130,009
Messages
5,639,532
Members
417,095
Latest member
danager2020

### 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.

### Which adblocker are you using?

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

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