Duplicates and Sort Formula

[kyle_04]

Hi,

I have been trying to find a solution for removing duplicates and sorting data in alphabetical order. I found a couple of formulas but the following is the closest I have got for what I need. However it doesn't sort the data in alphabetical order (Leaving any zeros at the bottom, or deleting out if possible). Formulas in sheetA as follows:

Excel Workbook
	A	B
2	MASTERFILE NUMBER	67390
3
4
5	Order Number
6	82513
7	82519
8
9	2345
10
11
SheetA

Column A is pulling data from the following sheetB:

Excel Workbook
	B	C
2	82524	60657
3	82513	67390
4	82519	67390
5		67390
6	82520	88903
7	2345	67390
8	82519	67390
sheetB

As you can see the formula in sheetA (Column A) is working and removing duplicates (82519 for example), and is based on the criteria set out in B2 on sheetA (linked to column C in sheetB), however it is putting them in the order it finds them, including any blank spaces. Is there any way round this?

Kyle

 

[Akashwani]

Hi Kyle,

Take a look here.....

http://www.youtube.com/user/ExcelIsFun#p/search/0/IZLAzIYfMDU

Good luck.


Ak

 

[Akashwani]

Hi Kyle,

If you couldn't get any joy with the link, perhaps this will work for you...

Excel Workbook
	B	C
1	Order Numbers	Master File Numbers
2	82524	60657
3	82513	67390
4	82519	67390
5		67390
6	82520	88903
7	2345	67390
8	82519	67390
9	82524	60657
10	82513	67390
11	89999	67390
12	82520	88903
13	82519	67390
SheetB

Excel Workbook
	A	B	C
1	*	*	*
2	Master File Number	67390	8
3	*	*	*
4	*	*	*
5	Order Numbers	*	*
6	2345	*	*
7	82513	*	*
8	82519	*	*
9	89999	*	*
10	#NUM!	*	*
11	#NUM!	*	*
12	#NUM!	*	*
13	#NUM!	*	*
14	*	*	*
SheetA

The formula in A6 needs entering with ctrl - shift - enter NOT enter, you can then copy it down as far as is required. I'm unsure how best to avoid the #NUM! beside using this formula.....

=IF(ISERR(IF(ROWS(A$6:A6)<=$C$2,SMALL(IF(SheetB!$C$2:$C$13=$B$2,IF(ISNA(MATCH(SheetB!$B$2:$B$13,SheetA!$A$5:A5,0)),SheetB!$B$2:$B$13)),1),"")),"",IF(ROWS(A$6:A6)<=$C$2,SMALL(IF(SheetB!$C$2:$C$13=$B$2,IF(ISNA(MATCH(SheetB!$B$2:$B$13,SheetA!$A$5:A5,0)),SheetB!$B$2:$B$13)),1),""))

I hope that helps you.

Good luck.

Ak

 

[kyle_04]

Thanks for the reply,

I have already watched that helpful video, but the formula kind of evolved since then.

The formula posted works perfectly, the only issue is that when I add it into sheetA cell C6 a space appears, not sure if that happens with you?


Kyle

 

[Akashwani]

Hi Kyle,

That's interesting and this is baffling.
I have just reproduced the data and formula and I now get this....

Excel Workbook
	A	B	C
1	*	*	*
2	Master File Number	67390	8
3	*	*	*
4	*	*	*
5	*	*	Order Numbers
6	*	*	0
7	*	*	2345
8	*	*	82513
9	*	*	82519
10	*	*	89999
11	*	*	#NUM!
12	*	*	#NUM!
13	*	*	#NUM!
SheetA

I'm unsure as to why this has happened, obviously the formula I gave is flawed, sorry about that, my limited knowledge prevents me from correcting it

Hopefully someone will read this and provide a more accurate formula for you.

Ak

 

[Akashwani]

Hi Kyle,

Try this in C6

=IF(ISERR(IF(ROWS(C$6:C6)<=$C$2,SMALL(IF(SheetB!$C$2:$C$13=$B$2,IF(ISNA(MATCH(SheetB!$B$2:$B$13,SheetA!$C$5:C5,0)),SheetB!$B$2:$B$13)),ROWS(C$6:C6)+1),"")),"",IF(ROWS(C$6:C6)<=$C$2,SMALL(IF(SheetB!$C$2:$C$13=$B$2,IF(ISNA(MATCH(SheetB!$B$2:$B$13,SheetA!$C$5:C5,0)),SheetB!$B$2:$B$13)),ROWS(C$6:C6)+1),""))

Excel Workbook
	A	B	C
1
2	Master File Number	67390	8
3
4
5			Order Numbers
6			2345
7			82513
8			82519
9
10
11
12
13
SheetA

Hopefully that has resolved the problem for you, fingers crossed.

Ak

 

[kyle_04]

Nice try but it didn't work, the record 82513 didn't show up.

Not to worry I'll figure a way round it,

Thanks,

Kyle

 

[Akashwani]

Hi Kyle,

Take a look at my sample file here....


Kyle04.xls

Excel Workbook
	B	C
1	B	C
2	82524	60657
3	82513	67390
4	82519	67390
5		67390
6	82520	88903
7	2345	67390
8	82519	67390
SheetB

Excel Workbook
	A	B	C
1	*	*	*
2	MASTERFILE NUMBER	67390	3
3			*
4			*
5	*		Order Number
6	*		2345
7	*		82513
8	*		82519
9	*		*
SheetA

I hope that works for you.

Ak