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.

 

[Fluff]

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?

 

[Padthelad]

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]

Ok, how about

Sub Padthelad()
   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
            Dic.Add Cl.Value, Nothing
            If Evaluate("isref('" & Cl.Value & "'!A1)") Then
               Set Ws = Sheets(Cl.Value)
               Ws.Cells.Clear
            Else
               Sheets.Add(, Sheets(Sheets.Count)).Name = Cl.Value
               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.