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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows
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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Padthelad

Board Regular
Joined
May 13, 2016
Messages
64
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
Rich (BB code):
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,917
Messages
5,638,981
Members
417,061
Latest member
thematulaak

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
Top