Excel Forumula Help

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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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
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)))

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
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;""


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
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
Serial #Unique Sorted (XL 2007 & Higher)Unique Sorted (XL 2003)Unique Count

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

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

[TD="align: center"]2[/TD]
[TD="align: right"]427374[/TD]
[TD="align: right"][/TD]
[TD="align: right"]403392[/TD]
[TD="align: right"]403392[/TD]
[TD="align: right"]20[/TD]

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

[TD="align: center"]4[/TD]
[TD="align: right"]424908[/TD]
[TD="align: right"][/TD]
[TD="align: right"]412884[/TD]
[TD="align: right"]412884[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]424908[/TD]
[TD="align: right"][/TD]
[TD="align: right"]415317[/TD]
[TD="align: right"]415317[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]424908[/TD]
[TD="align: right"][/TD]
[TD="align: right"]419095[/TD]
[TD="align: right"]419095[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]427817[/TD]
[TD="align: right"][/TD]
[TD="align: right"]419978[/TD]
[TD="align: right"]419978[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]419978[/TD]
[TD="align: right"][/TD]
[TD="align: right"]421106[/TD]
[TD="align: right"]421106[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]419978[/TD]
[TD="align: right"][/TD]
[TD="align: right"]421852[/TD]
[TD="align: right"]421852[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]419978[/TD]
[TD="align: right"][/TD]
[TD="align: right"]422835[/TD]
[TD="align: right"]422835[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]427374[/TD]
[TD="align: right"][/TD]
[TD="align: right"]423549[/TD]
[TD="align: right"]423549[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]403392[/TD]
[TD="align: right"][/TD]
[TD="align: right"]423628[/TD]
[TD="align: right"]423628[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]423628[/TD]
[TD="align: right"][/TD]
[TD="align: right"]423731[/TD]
[TD="align: right"]423731[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]431237[/TD]
[TD="align: right"][/TD]
[TD="align: right"]424908[/TD]
[TD="align: right"]424908[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]415317[/TD]
[TD="align: right"][/TD]
[TD="align: right"]425120[/TD]
[TD="align: right"]425120[/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]430984[/TD]
[TD="align: right"][/TD]
[TD="align: right"]426331[/TD]
[TD="align: right"]426331[/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]421852[/TD]
[TD="align: right"][/TD]
[TD="align: right"]427374[/TD]
[TD="align: right"]427374[/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]415317[/TD]
[TD="align: right"][/TD]
[TD="align: right"]427817[/TD]
[TD="align: right"]427817[/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]428581[/TD]
[TD="align: right"][/TD]
[TD="align: right"]428581[/TD]
[TD="align: right"]428581[/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]423731[/TD]
[TD="align: right"][/TD]
[TD="align: right"]430984[/TD]
[TD="align: right"]430984[/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]423731[/TD]
[TD="align: right"][/TD]
[TD="align: right"]431237[/TD]
[TD="align: right"]431237[/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]426331[/TD]
[TD="align: right"][/TD]

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

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

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

[TD="align: center"]24[/TD]
[TD="align: right"]419095[/TD]
[TD="align: right"][/TD]

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

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

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

[TD="align: center"]26[/TD]
[TD="align: right"]411826[/TD]
[TD="align: right"][/TD]

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

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

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

[TD="align: center"]28[/TD]
[TD="align: right"]419095[/TD]
[TD="align: right"][/TD]

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

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

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

[TD="align: center"]30[/TD]
[TD="align: right"]422835[/TD]
[TD="align: right"][/TD]

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

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

[TD="align: center"]32[/TD]
[TD="align: right"]422835[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]34[/TD]
[TD="align: right"]425120[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]36[/TD]
[TD="align: right"]412884[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]38[/TD]
[TD="align: right"]421106[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]{=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))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C3[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D3[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]


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

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:
[TABLE="width: 384"]
<TBODY>[TR]
[TD]unsorted</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]sorted</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]year</SPAN>[/TD]
[TD="colspan: 2"]Maximum gust </SPAN>[/TD]
[TD][/TD]
[TD]year</SPAN>[/TD]
[TD]Maximum gust </SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1952</SPAN>[/TD]
[TD="align: right"]31.4</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1966</SPAN>[/TD]
[TD="align: right"]23.7</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1953</SPAN>[/TD]
[TD="align: right"]33.4</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1997</SPAN>[/TD]
[TD="align: right"]24.7</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1954</SPAN>[/TD]
[TD="align: right"]29.8</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1964</SPAN>[/TD]
[TD="align: right"]25.2</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1955</SPAN>[/TD]
[TD="align: right"]30.3</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1974</SPAN>[/TD]
[TD="align: right"]25.7</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1956</SPAN>[/TD]
[TD="align: right"]27.8</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1996</SPAN>[/TD]
[TD="align: right"]25.7</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1957</SPAN>[/TD]
[TD="align: right"]30.3</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1970</SPAN>[/TD]
[TD="align: right"]26.2</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1958</SPAN>[/TD]
[TD="align: right"]29.3</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1995</SPAN>[/TD]
[TD="align: right"]26.2</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1959</SPAN>[/TD]
[TD="align: right"]36.5</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1984</SPAN>[/TD]
[TD="align: right"]26.7</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1960</SPAN>[/TD]
[TD="align: right"]29.3</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1961</SPAN>[/TD]
[TD="align: right"]27.3</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1961</SPAN>[/TD]
[TD="align: right"]27.3</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1965</SPAN>[/TD]
[TD="align: right"]27.3</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1962</SPAN>[/TD]
[TD="align: right"]31.9</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1973</SPAN>[/TD]
[TD="align: right"]27.3</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1963</SPAN>[/TD]
[TD="align: right"]28.8</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1991</SPAN>[/TD]
[TD="align: right"]27.3</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1964</SPAN>[/TD]
[TD="align: right"]25.2</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1956</SPAN>[/TD]
[TD="align: right"]27.8</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1965</SPAN>[/TD]
[TD="align: right"]27.3</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1967</SPAN>[/TD]
[TD="align: right"]27.8</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1966</SPAN>[/TD]
[TD="align: right"]23.7</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1969</SPAN>[/TD]
[TD="align: right"]27.8</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1967</SPAN>[/TD]
[TD="align: right"]27.8</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1981</SPAN>[/TD]
[TD="align: right"]27.8</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1968</SPAN>[/TD]
[TD="align: right"]32.4</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1982</SPAN>[/TD]
[TD="align: right"]27.8</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1969</SPAN>[/TD]
[TD="align: right"]27.8</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1992</SPAN>[/TD]
[TD="align: right"]27.8</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1970</SPAN>[/TD]
[TD="align: right"]26.2</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1976</SPAN>[/TD]
[TD="align: right"]28.3</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1971</SPAN>[/TD]
[TD="align: right"]30.9</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1978</SPAN>[/TD]
[TD="align: right"]28.3</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1972</SPAN>[/TD]
[TD="align: right"]31.9</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1979</SPAN>[/TD]
[TD="align: right"]28.3</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1973</SPAN>[/TD]
[TD="align: right"]27.3</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1986</SPAN>[/TD]
[TD="align: right"]28.3</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1974</SPAN>[/TD]
[TD="align: right"]25.7</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1963</SPAN>[/TD]
[TD="align: right"]28.8</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1975</SPAN>[/TD]
[TD="align: right"]32.9</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1989</SPAN>[/TD]
[TD="align: right"]28.8</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1976</SPAN>[/TD]
[TD="align: right"]28.3</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1958</SPAN>[/TD]
[TD="align: right"]29.3</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1977</SPAN>[/TD]
[TD="align: right"]273</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1960</SPAN>[/TD]
[TD="align: right"]29.3</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1978</SPAN>[/TD]
[TD="align: right"]28.3</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1980</SPAN>[/TD]
[TD="align: right"]29.3</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1979</SPAN>[/TD]
[TD="align: right"]28.3</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1954</SPAN>[/TD]
[TD="align: right"]29.8</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1980</SPAN>[/TD]
[TD="align: right"]29.3</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1993</SPAN>[/TD]
[TD="align: right"]29.8</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1981</SPAN>[/TD]
[TD="align: right"]27.8</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1955</SPAN>[/TD]
[TD="align: right"]30.3</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1982</SPAN>[/TD]
[TD="align: right"]27.8</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1957</SPAN>[/TD]
[TD="align: right"]30.3</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1983</SPAN>[/TD]
[TD="align: right"]30.9</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1985</SPAN>[/TD]
[TD="align: right"]30.3</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1984</SPAN>[/TD]
[TD="align: right"]26.7</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1987</SPAN>[/TD]
[TD="align: right"]30.3</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1985</SPAN>[/TD]
[TD="align: right"]30.3</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1990</SPAN>[/TD]
[TD="align: right"]30.3</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1986</SPAN>[/TD]
[TD="align: right"]28.3</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1971</SPAN>[/TD]
[TD="align: right"]30.9</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1987</SPAN>[/TD]
[TD="align: right"]30.3</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1983</SPAN>[/TD]
[TD="align: right"]30.9</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1988</SPAN>[/TD]
[TD="align: right"]34</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1994</SPAN>[/TD]
[TD="align: right"]30.9</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1989</SPAN>[/TD]
[TD="align: right"]28.8</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1952</SPAN>[/TD]
[TD="align: right"]31.4</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1990</SPAN>[/TD]
[TD="align: right"]30.3</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1962</SPAN>[/TD]
[TD="align: right"]31.9</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1991</SPAN>[/TD]
[TD="align: right"]27.3</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1972</SPAN>[/TD]
[TD="align: right"]31.9</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1992</SPAN>[/TD]
[TD="align: right"]27.8</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1968</SPAN>[/TD]
[TD="align: right"]32.4</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1993</SPAN>[/TD]
[TD="align: right"]29.8</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1975</SPAN>[/TD]
[TD="align: right"]32.9</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1994</SPAN>[/TD]
[TD="align: right"]30.9</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1953</SPAN>[/TD]
[TD="align: right"]33.4</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1995</SPAN>[/TD]
[TD="align: right"]26.2</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1988</SPAN>[/TD]
[TD="align: right"]34</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1996</SPAN>[/TD]
[TD="align: right"]25.7</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1959</SPAN>[/TD]
[TD="align: right"]36.5</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1997</SPAN>[/TD]
[TD="align: right"]24.7</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1998</SPAN>[/TD]
[TD="align: right"]42.2</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1998</SPAN>[/TD]
[TD="align: right"]42.2</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1977</SPAN>[/TD]
[TD="align: right"]273</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL span=6></COLGROUP>[/TABLE]


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

Forum statistics

Threads
1,223,275
Messages
6,171,119
Members
452,381
Latest member
Nova88

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