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.

 

[Peter_SSs]

Actually, looking again at your sample data in post #8 , I'm somewhat confused. In that screen shot of Alphabet Copypaste, column GO appears identical to column GJ. Is that the case all the way down the data?
If so, won't the table in 'Alphabet calculation' just be a copy of the table in 'Alphabet Copypaste and the formula in Alphabet calculation to be copied across and down would just need to be ='Alphabet Copypaste'!A4 with no helper cells anywhere.

I assume I must be wrong somewhere but perhaps you can clarify about columns GO and GJ in Alphabet Copypaste??

 

[Eamonn100]

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 )

Sorry Peter for not answering that question. Yes row 4.

Ok I'll give this a go.

 

[Peter_SSs]

Ok I'll give this a go.

Check post #11 first.

 

[Eamonn100]

Actually, looking again at your sample data in post #8 , I'm somewhat confused. In that screen shot of Alphabet Copypaste, column GO appears identical to column GJ. Is that the case all the way down the data?
If so, won't the table in 'Alphabet calculation' just be a copy of the table in 'Alphabet Copypaste and the formula in Alphabet calculation to be copied across and down would just need to be ='Alphabet Copypaste'!A4 with no helper cells anywhere.

I assume I must be wrong somewhere but perhaps you can clarify about columns GO and GJ in Alphabet Copypaste??

Peter column GO is a column that sorts alphabetically column GI but it doesn't take into account for duplicates. It's really just an old "vestigial" formula that after posting my first screenshots yesterday I realised that I my not need it anymore. I think column GI could be used?......... but maybe best to continue with GO for now just in case it has some function that I can't remember from when I set it up 6 months ago. Once I get a full day to go over/test it I can then decide weather to dump it or not?

As for the order of GI and GO being the same that's just from my playing about with things. When things are being used GI is pasted not in alphabetical order.

 

[Eamonn100]

Well I'm sure of one thing Peter. If this was 400 years ago you would be getting burnt at the stake tomorrow morning for witchcraft.

That's seems to be working 100%. I'll take a breather (as I spent 6 hours deleting the 5000 rows of the old formula this morning..... it took that long) then I'll give it the full conformation look over but it seems to be fine.

I'll post back after a rest.

 

[Eamonn100]

Check post #11 first.

Prefect Peter. I've went over it and can't see any problems with it. Thanks for that.

Next on the list is becoming accustomed to the HTML Maker. Most of the time when I post here I'm already mentally drained and when someone asks for clarification it's hard to explain in words what I'm trying to do, so the HTML Maker will help with that.

Thanks.