Excel Forumula Help

michael_allen_24 · Dec 29, 2010

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

tagman · Apr 1, 2011

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.

kyle_04 · May 17, 2011

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

Sandeep Warrier · May 18, 2011

Hi Kyle,

For this you will need the formula posted in post number 37.

kyle_04 · May 18, 2011

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

A

B

C

2

MASTERFILE NUMBER

67390

3

4

5

Order Number

6

7

8

9

SheetA

Excel Workbook

B

C

2

82524

60657

3

82513

67390

4

82519

67390

5

82520

88903

6

2345

67390

7

82519

67390

sheetB

Much appreciated,

Kyle

Jon von der Heyden · May 18, 2011

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

sabi.kiss · May 18, 2011

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

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

kyle_04 · May 18, 2011

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

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

MoBo · Jan 2, 2013

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

A B C D E
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

Matty · Jan 3, 2013

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

cicak · May 31, 2013

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

Excel Forumula Help

michael_allen_24

Board Regular

tagman

Board Regular

Excel Facts

kyle_04

Board Regular

Sandeep Warrier

Well-known Member

kyle_04

Board Regular

Jon von der Heyden

MrExcel MVP, Moderator

sabi.kiss

Board Regular

kyle_04

Board Regular

MoBo

New Member

Matty

Well-known Member

cicak

New Member

Similar threads

Forum statistics

Share this page

	A	B	C
Serial #	Unique Sorted (XL 2007 & Higher)	Unique Sorted (XL 2003)	Unique Count

Excel Forumula Help

Board Regular

Board Regular

Excel Facts

Board Regular

Well-known Member

Board Regular

MrExcel MVP, Moderator

Board Regular

Board Regular

New Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock