=countdiff

How_Do_I

How_Do_I

Well-known Member
Joined
Oct 23, 2009
Messages
1,831
Office Version
  1. 2010
Platform
  1. Windows
Sort by date Sort by votes
Give me a second please Aladin... too many workbooks open so I switched to manual calculation and I'm not sure I have a fault now... Will post here to let you know...
 
Upvote 0

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Grrrrr.... The count is right (A1) but it's leaving the blank and actually pulling out one team too few :(
 
Upvote 0
Worse still...

It is actually working for the first 2 columns of the "Home Teams"... then it leaves a blank in the first cell with the formula and extracts one team too few... :(
 
Last edited:
Upvote 0
How_Do_I said:
Worse still...

It is actually working for the first 2 columns of the "Home Teams"... then it leaves a blank in the first cell with the formula and extracts one team too few... :(
Click to expand...

Please slow down... No need to post so fast and so much! And try not post that many formula instances...

What are the definitions of HomeWin, Headers, Home, and Away the exhibit in this thread is using?
 
Upvote 0
Cool... I'm calm...!!!

Thanks Aladin :)

Headers =Sheet1!$C$5:$Z$5

HomeWin =OFFSET(Sheet1!$C$6,0,0,23,24)

I've now noticed this... UNIQUEVALVES... if I change the order to 0 desending... what I already have "seems" to be working... How strange is that...

Oh and Home ='FT Scores'!$C$2:$C$553

Away ='FT Scores'!$D$2:$D$553
 
Last edited:
Upvote 0
How_Do_I said:
Cool... I'm calm...!!!

Thanks Aladin :)

Headers =Sheet1!$C$5:$Z$5

HomeWin =OFFSET(Sheet1!$C$6,0,0,23,24)
Click to expand...

Great.

I'm now noticed this... UNIQUEVALVES... if I change the order to 0 desending... what I already have "seems" to be working... How strange is that...
Click to expand...

Be patients. When the order is 0, the function tries to create a list in descending order. A formula blank (which is not the same as an empty cell) slides down to the end of the list, while when the order is 1 it comes first. Below I'm using an exhibit (A1:C12) similar to yours, where the colored cells (C8 and C11) contains formula blanks (generated with ="").

<TABLE style="WIDTH: 301pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=401><COLGROUP><COL style="WIDTH: 126pt; mso-width-source: userset; mso-width-alt: 5973" width=168><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 127pt; mso-width-source: userset; mso-width-alt: 6001" width=169><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 126pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=168 align=right>9</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=xl65 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 127pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=169></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Barnsley</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=xl65>Barnsley</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Blackpool</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=xl65>West Bromwich Albion</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Cardiff City</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=xl65>Ipswich Town</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Ipswich Town</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=xl65>Cardiff City</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Leicester City</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=xl65>Middlesbrough</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Middlesbrough</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=xl65>Leicester City</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Nottingham Forest</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: #ffccff; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Watford</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=xl65>Blackpool</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>West Bromwich Albion</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=xl65>Nottingham Forest</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19></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: #ffccff; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19></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=xl65>Watford</TD></TR></TBODY></TABLE>

A1:
Code: 
=COUNTDIFF(C2:C12,,"")

A2, control+shift+enter, not just enter, and copy down:
Code: 
=IF(ROWS($A$2:A2)<=$A$1,
    INDEX(UNIQUEVALUES(IF($C$2:$C$12="",
     REPT("z",255),$C$2:$C$12),1),ROWS($A$2:A2)),"")

The desire to create an ascending order list requires as to invoke an array formula.
 
Upvote 0
Hi Aladin, :)
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> </o:p>
I’m sorry to say that I’m now totally confused and other than start from the beginning I have no idea what to do.
<o:p> </o:p>
So my Jeanie is an extract of my worksheet…
<o:p> </o:p>
The worksheet contains the following named ranges…
<o:p> </o:p>
Headers =Sheet1!$E$5:$AB$5
<o:p> </o:p>
HomeWin =Sheet1!$E$6:$AB$28
<o:p> </o:p>
C3 = Data Validation of the 24 teams... only 2 are in the Jeanie
<o:p> </o:p>
So as the Jeanie is now, I want the 13 teams F6:F18 to be extracted to C6:C18 alphabetically.
<o:p> </o:p>
There will never be a blank in column F or in any of the other columns (E-AB), the problem I had was the blank in C6 plus the secondary issue of only some of the teams being extracted in C7 downwards.
<o:p> </o:p>
Then if I selected “<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:eek:ffice:smarttags" /><st1:City w:st="on"><st1:place w:st="on">Bristol</st1:place></st1:City>” (not in the Jeanie) in C3 I want the teams in that column of the range “HomeWin” to be in C6 downwards…
<o:p> </o:p>
I really do appreciate your help Aladin and hopefully, this post will clear up what I’m trying to achieve…


Excel Workbook
CDEF
213Home WinsW
3Blackpool
42813
5TeamsBarnsleyBlackpool
61West Bromwich AlbionCoventry City
72Ipswich TownNewcastle United
83Cardiff CityPeterborough United
94MiddlesbroughPlymouth Argyle
105Leicester CitySheffield United
116WatfordScunthorpe United
127BlackpoolWatford
138Nottingham ForestMiddlesbrough
149Reading
1510Ipswich Town
1611Swansea City
1712Doncaster Rovers
1813Nottingham Forest
Sheet1
 
Upvote 0
Aladin...

I know what you've said about posting etc... BUT, I just want to post these two Jeanies so you can see exactly what is going on...

I've changed the team by using the validation in C3 so you only see 1 of the 24 columns of teams...

This is UNIQUEVALES set to 1... It misses the last team out "West Bromwich Albion" and leaves a blank in C6.

Excel Workbook
CDE
1
28Home Wins
3Barnsley
418
5TeamsBarnsley
6 1West Bromwich Albion
7Blackpool2Ipswich Town
8Cardiff City3Cardiff City
9Ipswich Town4Middlesbrough
10Leicester City5Leicester City
11Middlesbrough6Watford
12Nottingham Forest7Blackpool
13Watford8Nottingham Forest
Sheet1
 
Upvote 0
I think you missed the gist of my post...

C2:

=COUNTDIFF(INDEX(HomeWin,0,C4),,"")

C3:

=MATCH(C3,Headers,0)

C6, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=IF(ROWS($C$6:C6)<=$C$2,
   INDEX(UNIQUEVALUES(IF(INDEX(HomeWin,0,$C$4)="",REPT("z",255),
     INDEX(HomeWin,0,$C$4)),1),ROWS($C$6:C6)),"")

I have modified from your post the formula in C6 in accordance with my last post...
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
conditional formatting question
Replies
2
Views
153
kerm007
K
N
  • Question
Using the IF
Replies
1
Views
66
etaf
etaf
N
  • Solved
COUNTIF With Multiple Criteria From Different Ranges
Replies
3
Views
98
Alex Blakenburg
A
A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
A
  • Solved
Date written need to extract into date format
2
Replies
15
Views
357
Joe4
Joe4

Forum statistics

Threads
1,214,653
Messages
6,120,748
Members
448,989
Latest member
mariah3

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