Select Column based on amount of data available

nostradamus

Board Regular
Joined
Aug 9, 2010
Messages
143
Office Version
  1. 365
Platform
  1. Windows
Dear Formula Wizs,
I have a big spreadsheet with about 100 rows and close to 300 columns, below is just a subsection of the whole data.
1) So, I would like to for the formula in Cell B19 to reflect the Column that has the most number of data entries from Cols B-K.
2) Col B20 would reflect the Column that has next most number of data that is potentially missing from the First Pick..
3) Col B21 reflects Column with data missing from First and Second Picks.
4) ... etc...
Your help would be much appreciated :)

excelformula.xlsx
ABCDEFGHIJKL
1CountyDet-1Det-2Det-3Det-4Det-5Det-6Det-7Det-8Det-9Det-10
2Maverick County21353
3Jim Hogg County1631
4Zavala County5523866
5Dallas County215
6Liberty County343
7Callahan County8553
8Cooke County35
9Randall County316
10Hockley County59
11Aransas County4131111
12Hardeman County635
13Brazos County236
14Collin County219
15Bell County3115
16Fayette County221
17Atascosa County1611
18
19First PickDet-8
202nd Pick (missing info from First Pick)
213rd Pick (missing info from 1st & 2nd Pick)
224th Pick (missing info from 1st & 3rd Pick)
235th Pick (missing info from 1st - 4th Pick)
246th Pick (missing info from 1st -5th Pick)
257th Pick (missing info from 1st - 6th Pick)
268th Pick (missing info from 1st - 7th Pick)
27
Sheet1
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
What is expected result for B20 and B21 ?
 
Upvote 0
What is expected result for B20 and B21 ?
Col B19 has "Det-8" because Col I had most number of County occurrences.
Col B20 would have the next best info about "Counties" not covered by "Det-8" - so it would be "Det-9" because if has most info from the other Counties.
Then B21-B26 would follow suit so on and so forth, until all the counties are covered.
 
Upvote 0

Forum statistics

Threads
1,215,972
Messages
6,128,024
Members
449,414
Latest member
sameri

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