Duplicate removal not in alphabetical order

masud8956

Board Regular
Joined
Oct 22, 2016
Messages
163
Office Version
  1. 2016
  2. 2011
  3. 2007
Platform
  1. Windows
Hi,

I am using the following formula in my worksheet:

=IFERROR(INDEX($H$74:$H$783,MATCH(0,COUNTIF($H$74:$H$783,"<"&$H$74:$H$783)-SUM(COUNTIF($H$74:$H$783,F$797:F797)),0)),"")

I am getting the intended list that i want with duplicates removed. But the List is appearing in alphabetical order.

What modification do I need to keep the order unchanged? I want it in the order of input.

I prefer NOT to use VBA.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
could you work with this
with the List in A1 to A19
in F2=LOOKUP(REPT("z",255),CHOOSE({1;2},"",INDEX($A$1:$A$19,MATCH(TRUE,INDEX(ISNA(MATCH($A$1:$A$19,$F$1:$F1,0)),0),0)))) fill down
 
Upvote 0
could you work with this
with the List in A1 to A19
in F2=LOOKUP(REPT("z",255),CHOOSE({1;2},"",INDEX($A$1:$A$19,MATCH(TRUE,INDEX(ISNA(MATCH($A$1:$A$19,$F$1:$F1,0)),0),0)))) fill down

Thank you very much for the quick reply!!

The formula worked beautifully. Only one problem though. I have got a blank cell in between the listed items. Like:

A
B

C
D
E

So, C is returned but after a blanked row. I forgot to mention that in my list between A1 and A19 I have some blank cells and those blanks are necessary.

Thanks in advance!!
 
Last edited:
Upvote 0
If Formula is in F798

=IFERROR(INDEX($H$74:$H$783,SMALL(IF((COUNTIF(F$797:F797,$H$74:$H$783)=0)*($H$74:$H$783<>0),ROW($H$74:$H$783),""),1)-ROW($H$74)+1),"")
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,244
Members
452,622
Latest member
Laura_PinksBTHFT

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