Excel Forumula Help

M

michael_allen_24

Board Regular
Joined
Dec 29, 2010
Messages
64
I have a list of names such as:

1. Kay
2. Roy
3. Jay
4. Kay
5. Pete
6. Roy
7. Kay
8. Tom

These names might be repeating, such as Kay appearing 3 times above. How do I create a forumula to pull back the names in alphabetical order? But obviously omitting the repeating values? So if the forumula worked correctly I would have:

1. Jay
2. Kay
3. Pete
4. Roy
5. Tom

Thanks in advance
 
Sort by date Sort by votes
I've cracked it! I filtered the data after sorting out the unique values and it worked a treat. After that little brainwave I finished of the whole sheet including writing the other formulas I needed in about half an hour. Thank you very much for your help, I genuinely couldn't have done it with you.
 
Upvote 0

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Is there anyway to adapt this formula to depend on what is in another column.

For example in the picture below, how would we get the results with a 1 in column A, which would display in column D as:

Jo
Sioux
Chin

Using the formula outlined earlier in the thread:

{=IFERROR(INDEX($A$2:$A$10,MATCH(0,IF(LEN($A$2:$A$10)>0,COUNTIF($A$2:$A$10,"<"&$A$2:$A$10),-1)-SUM(COUNTIF($A$2:$A$10,$B$1:B1)),0)),"")}


Thanks,

Kyle
kyle120284
 
Upvote 0
Thanks for the reply Sandeep,

Im not sure if post 37 will work for me (If I am reading it correctly), for two reasons:

1) its based on maximum number of characters
2) the helper column is linked to the criteria, and my document will have more than one criteria meaning that I would need multiple helper columns.

I was going to start a new thread but I think this is relevant to this post.

I have found a formula that works, but doesn't remove the duplicates, is there anyway to adapt this to remove the duplicates and zeros (As my skills are no where near good enough to do this). SheetA is where the data is being pulled into based on cell C2, which works but it shows duplicates, SheetB is where the data is stored:


Excel Workbook
ABC
2MASTERFILE NUMBER67390
3
4
5Order Number
682513
782519
82345
982519
SheetA




Excel Workbook
BC
28252460657
38251367390
48251967390
58252088903
6234567390
78251967390
sheetB



Much appreciated,

Kyle
 
Upvote 0
In SheetA B6:
Code: 
=LOOKUP(9.99E+307,CHOOSE({1;2},0,INDEX(SheetB!$B$2:$B$7,SMALL(IF((SheetB!$C$2:$C$7=$C$2)*ISERROR(MATCH(SheetB!$B$2:$B$7,SheetA!$B$5:B5,0)),ROW(SheetB!$2:$7)-1),1),0),0))

Copy down to end of range.

Format the resulting cells as:
0;0;""
 
Upvote 0
Haseeb Avarakkan said:
Another way,

<TABLE style="WIDTH: 366pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=488><COLGROUP><COL style="WIDTH: 16pt; mso-width-source: userset; mso-width-alt: 768" width=21><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 17pt; mso-width-source: userset; mso-width-alt: 841" width=23><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2267" width=62><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" width=107><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 16pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=21></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 62pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=83>
A​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=64>
B​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 17pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=23>
C​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=62>
D​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=64>
E​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 80pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=107>
F​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=64>
G​
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20>
1​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Category</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Ticker</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Extract</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Criteria</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Unique Count</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Helper</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20>
2​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Stocks</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>QWT</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>DDC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Stocks</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>
3​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>QWT</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20>
3​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Bonds</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>GGT</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>PAA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>ZZZZ</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20>
4​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>MutualFund</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>HSS</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>QWT</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>MAX Length</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>ZZZZ</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20>
5​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Stocks</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>QWT</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>
3​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>QWT</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20>
6​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Stocks</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>DDC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>DDC</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20>
7​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Bonds</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>ZZZZ</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20>
8​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>MutualFund</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>HSS</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>ZZZZ</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20>
9​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>MutualFund</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>MMO</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>ZZZZ</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20>
10​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Stocks</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>PAA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>PAA</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20>
11​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>MutualFund</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>WWW</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>ZZZZ</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=20>
12​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Bonds</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>NNY</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>ZZZZ</TD></TR></TBODY></TABLE>

F2, Confirmed with CTRL+SHIFT+ENTER,

Code: 
=SUM(IF(FREQUENCY(IF(A2:A12=E2,IF(B2:B12<>"",MATCH("~"&B2:B12,B2:B12&"",0))),ROW(A2:A12)-ROW(A2)+1),1))

F5, Confirmed with CTRL+SHIFT+ENTER

Code: 
=MAX(LEN(B2:B12))

G2, copy down

Code: 
=IF(AND(A2=$E$2,B2<>""),B2,REPT("Z",$F$5+1))

D2, Confirmed with CTRL+SHIFT+ENTER, copy down

Code: 
=IF(ROWS(D$2:D2)>$F$2,"",INDEX($B$2:$B$12,MATCH(0,COUNTIF($G$2:$G$12,"<"&$G$2:$G$12)-SUM(COUNTIF($G$2:$G$12,D$1:D1)),0)))
Click to expand...

Haseeb,

How would I integrate your formulas, into the data I've got.
Column A = Name field
Column B = Leave Start Date
Column C = Leave Finish Date
J4 = Pay Period Start date (which changes according to Pay Periods)
K4 = Pay Period End Date (which changes according to Pay Periods)

So, what I'm after is the actual employee names who are taking leave in between J4 & K4. I've tried modifying all your formulas however I'm getting no data whatsoever.
This is my formula:
=IF(ROWS(H$7:H7)>(SUM(IF(FREQUENCY(IF(($A$7:$A$12<>"")*($B$7:$B$12<>"")*($C$7:$C$12<>"")*($B$7:$B$12=$J$4)*($C$7:$C$12=$K$4),MATCH("~"&$A$7:$A$12&"",$A$7:$A$12&"",0)),ROW($A$7:$A$12)-ROW($A$7)+1),1))),"",INDEX($A$7:$A$12,MATCH(0,COUNTIF($G$7:$G$12,"<"&$G$7:$G$12)-SUM(COUNTIF($G$7:$G$12,H$6:H6)),0)))
Applied with CRTL+SHIFT+ENTER.
What am I doing wrong?
By the way, Column G is my Helper column & Column H is the actual Extract column where I would need the actual sorted names. Obviously I might have duplicates.

Thanks in advance.
Sabi
 
Upvote 0
Jon von der Heyden said:
In SheetA B6:
Code: 
=LOOKUP(9.99E+307,CHOOSE({1;2},0,INDEX(SheetB!$B$2:$B$7,SMALL(IF((SheetB!$C$2:$C$7=$C$2)*ISERROR(MATCH(SheetB!$B$2:$B$7,SheetA!$B$5:B5,0)),ROW(SheetB!$2:$7)-1),1),0),0))
Copy down to end of range.

Format the resulting cells as:
0;0;""
Click to expand...


Hi John, thanks for this, it seems to work but if there are blank lines in column B on SheetB then it also displays them as blanks in SheetA, is there any expansion on the formula to remove the blanks, or maybe sort them in descending in order to leave the blanks at the bottom (Like the other formula posted by Sandeep),

Many thanks,

Kyle
 
Upvote 0
Sandeep Warrier said:
The only difference would be that in 2003 you will not be able to use IFERROR.

If you still face problems, can you upload your workbook on a file upload site like box.net, and post the link here?

Excel 2010
ABCDE
1Serial #Unique Sorted (XL 2007 & Higher)Unique Sorted (XL 2003)Unique Count
242737440339240339220
3423549411826411826
4424908412884412884
5424908415317415317
6424908419095419095
7427817419978419978
8419978421106421106
9419978421852421852
10419978422835422835
11427374423549423549
12403392423628423628
13423628423731423731
14431237424908424908
15415317425120425120
16430984426331426331
17421852427374427374
18415317427817427817
19428581428581428581
20423731430984430984
21423731431237431237
22426331
23421852
24419095
25
26411826
27
28419095
29
30422835
31
32422835
33
34425120
35
36412884
37
38421106

<colgroup><col style="width: 10pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​

Array Formulas
CellFormula
C2{=IFERROR(INDEX($A$2:$A$500,MATCH(0,COUNTIF($A$2:$A$500,"<"&$A$2:$A$500)-SUM(COUNTIF($A$2:$A$500,$C$1:C1)),0)),"")}
D2{=IF(ROWS($D$1:D1)<=$E$2,INDEX($A$2:$A$500,MATCH(0,COUNTIF($A$2:$A$500,"<"&$A$2:$A$500)-SUM(COUNTIF($A$2:$A$500,$C$1:C1)),0)),"")}
E2{=SUM(IF(FREQUENCY(IF($A$2:$A$500<>"",MATCH("~"&$A$2:$A$500&"",$A$2:$A$500&"",0)),ROW($A$2:$A$500)-ROW($A$2)+1),1))}
C3{=IFERROR(INDEX($A$2:$A$500,MATCH(0,COUNTIF($A$2:$A$500,"<"&$A$2:$A$500)-SUM(COUNTIF($A$2:$A$500,$C$1:C2)),0)),"")}
D3{=IF(ROWS($D$1:D2)<=$E$2,INDEX($A$2:$A$500,MATCH(0,COUNTIF($A$2:$A$500,"<"&$A$2:$A$500)-SUM(COUNTIF($A$2:$A$500,$C$1:C2)),0)),"")}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
Click to expand...


Hi,

I'm looking for solution with combined text and numbers fields in Serial column.

Is there any simple modification of presented formulas, or I must use totally new approach.

Regards,

Bostjan
 
Last edited:
Upvote 0
Hi,

I'm looking for solution with combined text and numbers fields in Serial column.

Is there any simple modification of presented formulas, or I must use totally new approach.

Regards,

Bostjan
Click to expand...

Hi,

You're question isn't clear.

Can I suggest you create a new Thread for your question and add in as much detail as possible. This will better ensure you get the answer you're after.

Matty
 
Upvote 0
Hi,

I"m new here, and I'm not sure if I'm being rude to ask another question about EXcel Magic Tricks #759, but here goes.

That formula worked like a charm for me once I figured out the trick to entering that aray formula into cell C2 and then copying the array formula down to fill up the table in Col C. I had tried to enter the array formula into cells C2:C20, and it entered the formulas but the formulas didn't work.

Now, I have a slightly different application that requires a slifghtly differnt sort. I need to take a list, cull out all the blanks, then sort it, except I need to leave in all the duplicates.
it needs to look like the:
unsorted</SPAN>sorted</SPAN>
year</SPAN>Maximum gust </SPAN>year</SPAN>Maximum gust </SPAN>
1952</SPAN>31.4</SPAN>1966</SPAN>23.7</SPAN>
1953</SPAN>33.4</SPAN>1997</SPAN>24.7</SPAN>
1954</SPAN>29.8</SPAN>1964</SPAN>25.2</SPAN>
1955</SPAN>30.3</SPAN>1974</SPAN>25.7</SPAN>
1956</SPAN>27.8</SPAN>1996</SPAN>25.7</SPAN>
1957</SPAN>30.3</SPAN>1970</SPAN>26.2</SPAN>
1958</SPAN>29.3</SPAN>1995</SPAN>26.2</SPAN>
1959</SPAN>36.5</SPAN>1984</SPAN>26.7</SPAN>
1960</SPAN>29.3</SPAN>1961</SPAN>27.3</SPAN>
1961</SPAN>27.3</SPAN>1965</SPAN>27.3</SPAN>
1962</SPAN>31.9</SPAN>1973</SPAN>27.3</SPAN>
1963</SPAN>28.8</SPAN>1991</SPAN>27.3</SPAN>
1964</SPAN>25.2</SPAN>1956</SPAN>27.8</SPAN>
1965</SPAN>27.3</SPAN>1967</SPAN>27.8</SPAN>
1966</SPAN>23.7</SPAN>1969</SPAN>27.8</SPAN>
1967</SPAN>27.8</SPAN>1981</SPAN>27.8</SPAN>
1968</SPAN>32.4</SPAN>1982</SPAN>27.8</SPAN>
1969</SPAN>27.8</SPAN>1992</SPAN>27.8</SPAN>
1970</SPAN>26.2</SPAN>1976</SPAN>28.3</SPAN>
1971</SPAN>30.9</SPAN>1978</SPAN>28.3</SPAN>
1972</SPAN>31.9</SPAN>1979</SPAN>28.3</SPAN>
1973</SPAN>27.3</SPAN>1986</SPAN>28.3</SPAN>
1974</SPAN>25.7</SPAN>1963</SPAN>28.8</SPAN>
1975</SPAN>32.9</SPAN>1989</SPAN>28.8</SPAN>
1976</SPAN>28.3</SPAN>1958</SPAN>29.3</SPAN>
1977</SPAN>273</SPAN>1960</SPAN>29.3</SPAN>
1978</SPAN>28.3</SPAN>1980</SPAN>29.3</SPAN>
1979</SPAN>28.3</SPAN>1954</SPAN>29.8</SPAN>
1980</SPAN>29.3</SPAN>1993</SPAN>29.8</SPAN>
1981</SPAN>27.8</SPAN>1955</SPAN>30.3</SPAN>
1982</SPAN>27.8</SPAN>1957</SPAN>30.3</SPAN>
1983</SPAN>30.9</SPAN>1985</SPAN>30.3</SPAN>
1984</SPAN>26.7</SPAN>1987</SPAN>30.3</SPAN>
1985</SPAN>30.3</SPAN>1990</SPAN>30.3</SPAN>
1986</SPAN>28.3</SPAN>1971</SPAN>30.9</SPAN>
1987</SPAN>30.3</SPAN>1983</SPAN>30.9</SPAN>
1988</SPAN>34</SPAN>1994</SPAN>30.9</SPAN>
1989</SPAN>28.8</SPAN>1952</SPAN>31.4</SPAN>
1990</SPAN>30.3</SPAN>1962</SPAN>31.9</SPAN>
1991</SPAN>27.3</SPAN>1972</SPAN>31.9</SPAN>
1992</SPAN>27.8</SPAN>1968</SPAN>32.4</SPAN>
1993</SPAN>29.8</SPAN>1975</SPAN>32.9</SPAN>
1994</SPAN>30.9</SPAN>1953</SPAN>33.4</SPAN>
1995</SPAN>26.2</SPAN>1988</SPAN>34</SPAN>
1996</SPAN>25.7</SPAN>1959</SPAN>36.5</SPAN>
1997</SPAN>24.7</SPAN>1998</SPAN>42.2</SPAN>
1998</SPAN>42.2</SPAN>1977</SPAN>273</SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=6></COLGROUP>


I don't know how the formula in Trick #759 works, I hope someone can show me how to accomplish this with a formula that is equally effective.


Thanks,


cicak
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Solved
Count Number of Times Values appear in a Column and Rank top 3
Replies
5
Views
171
Fluff
Fluff
W
  • Question
Macro/VBA code code for creating new excel file for each pivot table from a column filter
Replies
1
Views
297
Anthony47
A
G
  • Solved
Removing duplicates rows
Replies
7
Views
353
Gcsingh90
G
M
  • Question
assigning one of three values to cell to get a score of the row
Replies
1
Views
111
dreid1011
dreid1011
T
  • Solved
Adding additional text to a value pulled from another sheet
Replies
8
Views
435
Snakehips
Snakehips

Forum statistics

Threads
1,221,899
Messages
6,162,682
Members
451,782
Latest member
LizN

Share this page

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