Index/Match and duplicates.

[Eamonn100]

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.

 

[dave2018]

please post some sample data (with duplicates) to simulate your real-life situation. (like 10-15 rows should be ok)

 

[Peter_SSs]

=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'?

 

[Eamonn100]

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)

 

[Eamonn100]

, and in what cell?

That's the first cell it's in. I made it so then it can be dragged over and down.

 

[Peter_SSs]

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(?)

=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))

 

[Eamonn100]

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(?)

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

 

[Eamonn100]

Excel 2013/2016
	A	B	C	D	E	GE	GF	GG	GH	GI	GJ	GK	GL	GM	GN	GO
1
2
3
4	0.028	25	25	0.028	0	-0.13	4.05	0.1	0	09YFHS	63					09YFHS
5	0.001	139.55	139.553	0.001	0	-0.03	0.23	0	0	14OPD	54					14OPD
6	0	200.83	202.525	0	9308	9.93	96.32	24.49	24.85	ABG	358					ABG
7	0	4.43	4.466	0	124.819	0.85	41.48	12.84	10.36	DGFY	652					DGFY
8	0	37.27	35.315	0	954.834	1.22	19.35	5.69	3.5	NK9IOQ	65					NK9IOQ
9	0	1.74	1.834	0	21.366	-0.17	11.14	1.19	0	PEF	23					PEF
Alphabet Copypaste

 

[Eamonn100]

Excel 2013/2016

	A	B	C	D	E	GF	GG	GH	GI	GJ
09YFHS	09YFHS
14OPD	14OPD
ABG	ABG
DGFY	DGFY
NK9IOQ	NK9IOQ
PEF	PEF

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.05[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]63[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]102[/TD]
[TD="align: right"]103.58[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2295.099[/TD]
[TD="align: right"]0.23[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]54[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]25[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]0.028[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]96.32[/TD]
[TD="align: right"]24.49[/TD]
[TD="align: right"]24.85[/TD]

[TD="align: right"]358[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]139.55[/TD]
[TD="align: right"]139.553[/TD]
[TD="align: right"]0.001[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]41.48[/TD]
[TD="align: right"]12.84[/TD]
[TD="align: right"]10.36[/TD]

[TD="align: right"]652[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]200.83[/TD]
[TD="align: right"]202.525[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9308[/TD]
[TD="align: right"]19.35[/TD]
[TD="align: right"]5.69[/TD]
[TD="align: right"]3.5[/TD]

[TD="align: right"]65[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]4.43[/TD]
[TD="align: right"]4.466[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]124.819[/TD]
[TD="align: right"]11.14[/TD]
[TD="align: right"]1.19[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]23[/TD]

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

 

[Peter_SSs]

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 )