Unique Lists from General Data Lists

[NotSoBoffin]

Good day,

I am having dificulty in creating a formula for my company.

We are having records on all nationalities, column A and the number of persons trained per nationlity in column B. I have VLOOKUP, to count/lookup the nationalties in one sheet and count how many trainees were trained per nationalities under another sheet. This now lists all nationalities under column A and the number of trainees per nationalities under column B.

I would to create a formula that will automatically, review column B, thus identifying the nationalities that we have trained, and sumarize the nationalities under column D and link the number of trainess with the relevant nationlaties trained under column E?

I tried consolidation, conditional formating but cannot get Excel to perform this function automatically.
[TABLE="width: 465"]
<COLGROUP><COL style="WIDTH: 220pt; mso-width-source: userset; mso-width-alt: 10715" width=293><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><TBODY>[TR]
[TD="class: xl67, width: 293, bgcolor: transparent"]Nationality:[/TD]
[TD="class: xl67, width: 90, bgcolor: transparent"]# of Trainees:[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="class: xl67, width: 81, bgcolor: transparent"]Nationality:[/TD]
[TD="class: xl67, width: 90, bgcolor: transparent"]# of Trainees:[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Bosnia And Herzegovina[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]Botswana[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Botswana[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]India[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Hungary[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]Malawi[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Iceland[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]Mozambique[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]India[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]Namibia[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Indonesia[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]Spain[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Madagascar[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]Zambia[/TD]
[TD="bgcolor: transparent, align: right"]24[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Malawi[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]Zimbabwe[/TD]
[TD="bgcolor: transparent, align: right"]27[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Morocco[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Mozambique[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Myanmar[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Namibia[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Solomon Islands[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Somalia[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]South Africa[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]South Georgia And The South Sandwich Islands[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Spain[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Sri Lanka[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Wallis And Futuna[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Western Sahara[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Yemen[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Zambia[/TD]
[TD="bgcolor: transparent, align: right"]24[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Zimbabwe[/TD]
[TD="bgcolor: transparent, align: right"]27[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]


Above is an example of what the end product should look like, done by auto formulae without copying and pasting.

Your assistance will be greatly appreciated!


NotSoBoffin

 

[drsarao]

Welcome to Board!
One way to do it:

Select A1:B1.
Data --> Filter --> Autofilter.
Click B1 down arrow and select Custom...
Select greater than and 0 (zero).

This would produce the desired result.

If needed, you can copy and paste elsewhere.

 

[T. Valko]

Good day,

I am having dificulty in creating a formula for my company.

We are having records on all nationalities, column A and the number of persons trained per nationlity in column B. I have VLOOKUP, to count/lookup the nationalties in one sheet and count how many trainees were trained per nationalities under another sheet. This now lists all nationalities under column A and the number of trainees per nationalities under column B.

I would to create a formula that will automatically, review column B, thus identifying the nationalities that we have trained, and sumarize the nationalities under column D and link the number of trainess with the relevant nationlaties trained under column E?

I tried consolidation, conditional formating but cannot get Excel to perform this function automatically.
[TABLE="width: 465"]
<TBODY>[TR]
[TD="class: xl67, width: 293, bgcolor: transparent"]Nationality:
[/TD]
[TD="class: xl67, width: 90, bgcolor: transparent"]# of Trainees:
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="class: xl67, width: 81, bgcolor: transparent"]Nationality:
[/TD]
[TD="class: xl67, width: 90, bgcolor: transparent"]# of Trainees:
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Bosnia And Herzegovina
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]Botswana
[/TD]
[TD="bgcolor: transparent, align: right"]11
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Botswana
[/TD]
[TD="bgcolor: transparent, align: right"]11
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]India
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Hungary
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]Malawi
[/TD]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Iceland
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]Mozambique
[/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]India
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]Namibia
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Indonesia
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]Spain
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Madagascar
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]Zambia
[/TD]
[TD="bgcolor: transparent, align: right"]24
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Malawi
[/TD]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"]Zimbabwe
[/TD]
[TD="bgcolor: transparent, align: right"]27
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Morocco
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Mozambique
[/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Myanmar
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Namibia
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Solomon Islands
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Somalia
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]South Africa
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]South Georgia And The South Sandwich Islands
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Spain
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Sri Lanka
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Wallis And Futuna
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Western Sahara
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Yemen
[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Zambia
[/TD]
[TD="bgcolor: transparent, align: right"]24
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Zimbabwe
[/TD]
[TD="bgcolor: transparent, align: right"]27
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]


Above is an example of what the end product should look like, done by auto formulae without copying and pasting.

Your assistance will be greatly appreciated!


NotSoBoffin

Try this...

Data in the range A2:B24

Summary area:

Book1
	D	E	F	G
1	Nationality	Trainees	Count	8
2	Botswana	11	_	_
3	India	1	_	_
4	Malawi	5	_	_
5	Mozambique	4	_	_
6	Namibia	1	_	_
7	Spain	2	_	_
8	Zambia	24	_	_
9	Zimbabwe	27	_	_
10	_	_	_	_
Sheet1

<br />
Enter this formula in G1. This will return the count of records that meet the criteria.

=COUNTIF(B:B,">0")

Enter this array formula** in D2:

=IF(ROWS(D$2:D2)>G$1,"",INDEX(A:A,SMALL(IF(B$2:B$24,ROW(B$2:B$24)),ROWS(D$2:D2))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Enter this formula in E2:

=IF(D2<>"",VLOOKUP(D2,A:B,2,0),"")

Select D2:E2 and copy down until you get blanks.