Unique Values from Multiple Sheets more than 4 sheets

[ravi2628]

Dear All,

good morning / afternoon /evening,

happy new year for all,

I was looking for a formula where I can get unique values from multiple sheets.

no macro please because who ever is using in my team is not much familiar with macros, so formula will help them much.

sample data is shared for reference.

Sheet1:

Country

Botswana

Botswana

Chile

Chile

Chile

Costarica

Dominican Republic

Dominican Republic

Dominican Republic

sheet 2

Country

Nigeria

Srilanka

Costarica

Philippines

Honduras

Uzbekistan

Peru

El Salvador

Bhutan

Syria

Dominican Republic

Guatemala

Myanmar

Panama

Chile

Nepal

Nicaragua

Kenya

sheet3:

Country

DR Congo

Georgia

Iran

Kenya

Namibia

Nepal

Nigeria

Philippines

Srilanka

Uganda

Yemen

Mozambique

Russia

Syria

Nicaragua

Botswana

El Salvador

Morocco

Thailand

Cambodia

Vietnam

Myanmar

Ecuador

sheet4:

Country

Colombia

Costarica

Cuba

Ghana

Peru

Kenya

Kyrgystan

Maldova

Myanmar

Nicaragua

North Sudan

Paraguay

Philippines

Russia

Srilanka

Uganda

Zambia

Nepal

Ecuador

Chile

Honduras

Bhutan

Syria

El Salvador

Jamaica

Georgia

Guatemala

Dominican Republic

Iran

Uzbekistan

Output:

Country

Bhutan

Botswana

Cambodia

Chile

Colombia

Costarica

Cuba

Dominican Republic

DR Congo

Ecuador

El Salvador

Georgia

Ghana

Guatemala

Honduras

Iran

Jamaica

Kenya

Kyrgystan

Maldova

Morocco

Mozambique

Myanmar

Namibia

Nepal

Nicaragua

Nigeria

North Sudan

Panama

Paraguay

Peru

Philippines

Russia

Srilanka

Syria

Thailand

Uganda

Uzbekistan

Vietnam

Yemen

Zambia

please help me out with the formula.

thanks in advance.

Regards,
Ravi

 

[Fluff]

How about

=LET(v,VSTACK(Sheet1!A2:A100,Sheet2!A2:A100,Sheet3!A2:A100,sheet4!A2:A100),SORT(UNIQUE(FILTER(v,v<>""))))

 

[ravi2628]

How about

=LET(v,VSTACK(Sheet1!A2:A100,Sheet2!A2:A100,Sheet3!A2:A100,sheet4!A2:A100),SORT(UNIQUE(FILTER(v,v<>""))))

thank you fluff the logic worked

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Peter_SSs]

If Sheets 1 to 4 are consecutive in your workbook then you could try

=LET(v,VSTACK(Sheet1:Sheet4!A1:A100),SORT(UNIQUE(FILTER(v,v<>""))))

 

[JvdV]

=SORT(UNIQUE(TOCOL(Sheet1:Sheet4!A2:A100,1)))

 

[Fluff]

That won't work if the OP has formulae in those cells that return ""