Index/Match and duplicates.

Eamonn100

Board Regular
Joined
Nov 12, 2015
Messages
156
I have a sheet with this formula. It finds an alphabetical list in column GO on a sheet called "'Alphabet CopyPaste". My problem is that my alphabetical list has duplicates in it and from reading online, my formula only finds the first duplicate. I've read about a few workarounds but I can't even begin to make them work. Can someone help with what I need to alter this formula to read/return duplicates as well.

=INDEX('Alphabet CopyPaste'!$A:$GJ,MATCH('Alphabet CopyPaste'!$GO4,'Alphabet CopyPaste'!$GI:$GI,0),COLUMN('Alphabet CopyPaste'!A4))

Thanks.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
please post some sample data (with duplicates) to simulate your real-life situation. (like 10-15 rows should be ok)
 
Upvote 0
=INDEX('Alphabet CopyPaste'!$A:$GJ,MATCH('Alphabet CopyPaste'!$GO4,'Alphabet CopyPaste'!$GI:$GI,0),COLUMN('Alphabet CopyPaste'!A4))
1. What sheet name is that formula on, and in what cell?

2. Which way do you want the multiple values listed (down or across)?

3. About how many rows of data will there be in sheet 'Alphabet CopyPaste'?
 
Last edited:
Upvote 0
1. What sheet name is that formula on, and in what cell?

2. Which way do you want the multiple values listed (down or across)?

3. About how many rows of data will there be in sheet 'Alphabet CopyPaste'?


Hi Peter,

1.Sheet name...........Alphabet calculation....... and the cell is A2.


2. Listed down the way.


3. 5100 rows.


I was working on this all morning and I got an array from the internet. I hatched it to work for my situation but being an array it's slowing things down a lot and I don't even know why I got it to work. Anyway so as not to confuse things I'll let you continue first before showing it. (I was just about to post it here to see if it can be sped up)
 
Last edited:
Upvote 0
You are calculating over quite a large array so calculation may be somewhat sluggish. However, try this in A2 of 'Alphabet calculation' and copied across and down. Guessing from your original formula, I have assumed that data in 'Alphabet CopyPaste' starts in row 4(?)

Code:
=INDEX('Alphabet CopyPaste'!$A$4:$GJ$6000,AGGREGATE(15,6,(ROW('Alphabet CopyPaste'!$GI$4:$GI$6000)-ROW('Alphabet CopyPaste'!$GI$4)+1)/('Alphabet CopyPaste'!$GI$4:$GI$6000='Alphabet CopyPaste'!$GO4),
COUNTIF('Alphabet CopyPaste'!$GO$4:$GO4,'Alphabet CopyPaste'!$GO4)),COLUMNS($A2:A2))
 
Upvote 0
You are calculating over quite a large array so calculation may be somewhat sluggish. However, try this in A2 of 'Alphabet calculation' and copied across and down. Guessing from your original formula, I have assumed that data in 'Alphabet CopyPaste' starts in row 4(?)

Code:
=INDEX('Alphabet CopyPaste'!$A$4:$GJ$6000,AGGREGATE(15,6,(ROW('Alphabet CopyPaste'!$GI$4:$GI$6000)-ROW('Alphabet CopyPaste'!$GI$4)+1)/('Alphabet CopyPaste'!$GI$4:$GI$6000='Alphabet CopyPaste'!$GO4),
COUNTIF('Alphabet CopyPaste'!$GO$4:$GO4,'Alphabet CopyPaste'!$GO4)),COLUMNS($A2:A2))

Thanks Peter.


It calculated for the duplicates no problem but except for speed. It is a little faster than my internet hatchet formula but still it takes 12 mins each time to calculate. The original non duplicate formula took about 10 secs to calculate. (It is dragged down 5100 rows and there's 192 columns. A:A-G:J). I spent time running the three different formulas twice after I seen the new formula was also slow at calculating. I just wanted to make sure that it wasn't something I was doing to be slowing down the new formula.


Is there any other way to speed it up,..eg........ Like inserting rows or would helper cells help?


I've downloaded the MrExcel HTML maker. I'm going to give it a go.
 
Upvote 0

Excel 2013/2016
ABCDEGEGFGGGHGIGJGKGLGMGNGO
1
2
3
40.02825250.0280-0.134.050.1009YFHS6309YFHS
50.001139.55139.5530.0010-0.030.230014OPD5414OPD
60200.83202.525093089.9396.3224.4924.85ABG358ABG
704.434.4660124.8190.8541.4812.8410.36DGFY652DGFY
8037.2735.3150954.8341.2219.355.693.5NK9IOQ65NK9IOQ
901.741.834021.366-0.1711.141.190PEF23PEF
Alphabet Copypaste
 
Last edited:
Upvote 0
Excel 2013/2016
ABCDEGFGGGHGIGJ
1
2
3
409YFHS00004.050.1009YFHS63
514OPD102103.5802295.0990.230014OPD54
6ABG25250.028096.3224.4924.85ABG358
7DGFY139.55139.5530.001041.4812.8410.36DGFY652
8NK9IOQ200.83202.5250930819.355.693.5NK9IOQ65
9PEF4.434.4660124.81911.141.190PEF23

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Alphabet Calulation
On Alphabet Copypaste I copypaste on to A:A to G:J. Columns G:K to G:N are helpers cells for a formula sorting the ID column(GI) on GO:GO. Alphabet Calulation uses column GO:GO on Alphabet Copypaste.
 
Last edited:
Upvote 0
Is there any other way to speed it up,..eg........ Like inserting rows or would helper cells help?
Not sure, but let's try in a copy of your workbook.

You didn't answer my question about the actual data in Alphabet CopyPaste starting at row 4 so I'm still assuming that is the case.

Also assuming that columns HA:HB in that sheet are free to use as helpers. Could use any columns though.

In 'Alphabet CopyPaste'
HA4: =GI4&"|"&COUNTIF(GI$4:GI4,GI4)
HB4: =ROWS(HB$4:HB4)
Copy both down to the end of the data.

In 'Alphabet calculation'
Insert 1 new row at the top of the sheet
In that new row, In C1 enter a 1, D1 enter a 2. Select C1:D1 and drag across to fill C1, D1, E1 etc with 1,2,3,4,... until you get to 191 (the number of columns in A:GJ in the Alphabet CopyPaste table

Insert 2 new columns at the left of the sheet.
In the new cell A3: ='Alphabet CopyPaste'!GO4
In the new cell B3: =VLOOKUP(A3&"|"&COUNTIF(A$3:A3,A3),'Alphabet CopyPaste'!HA$4:HB$6000,2,0)

In C3 (the old A2), copied across and down: =INDEX('Alphabet CopyPaste'!$A$4:$GJ$6000,$B3,C$1)

See how that goes (fingers crossed :))
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

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