How to get get cell valure based on parameters

Bpuad

New Member
Joined
Feb 17, 2012
Messages
36
Office Version
  1. 2019
Platform
  1. Windows
Sorry if the title didn't say it all.

I have a probelm which I can't seem to solve.

I have a list called "DATA" and in column A I have countries (Sweden, USA, France and so on) and in column B I have Citys for these countries (For USA It could be New York, Miami and so on) and in column C I have street names in this cities. Each country, city and streetname can appear several times in the list. If column A is USA the city in column B will alway be a US city and same goes for column C (will be a steet in the city in column B in the country in column A).

I then need to create another list base on the "DATA" list (in another sheet). This list needs to look in "DATA" list in column A and write in all unique entries (all countires in the list)
Like this:

Country
USA
Sweden
France

Column B need to list all the unique entries in column B for the value in B1 (USA in this case which is liked to A2 of cause)
Like this:

USA
New York
Miami
Las Vegas

Column C need to list all the unique entries in column C for the value in C1 (New York in this case which is liked to B2 of cause)
New York
Ann street
Fulton street
Perry street

Is this possible without VB programming? This would help me massivly!!

/Per
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Hi,

is this what you mean?....

Sample data....

Excel Workbook
ABC
1CountryCityStreet
2EnglandLondonLon Street
3USANew YorkNew Street
4FranceParisPar Street
5CanadaTorontoTor Street
6EnglandManchesterMan Street
7USAMiamiMia Street
8FranceNiceNic Street
9CanadaOttawaOtt Street
10EnglandOxfordOxf Street
11USALas VegasLas Street
12FranceCalaisCal Street
13CanadaVancouverVan Street
14EnglandLondonLon Street1
15USANew YorkNew Street1
16FranceParisPar Street1
17CanadaTorontoTor Street1
18EnglandManchesterMan Street1
19USAMiamiMia Street1
20FranceNiceNic Street1
21CanadaOttawaOtt Street1
22EnglandOxfordOxf Street1
23USALas VegasLas Street1
24FranceCalaisCal Street1
25CanadaVancouverVan Street1
DATA


Example results.....

Excel Workbook
ABCD
1CountriesEnglandLondon
2EnglandLondonLon Street
3USAManchesterLon Street1
4FranceOxford
5Canada
6
Results



You will need to change the sheet name and cell ranges to suit your layout.
The formula In A2 needs entering with ctrl shift enter NOT just enter, you can then copy it down.
The formula In B2 needs entering with ctrl shift enter NOT just enter, you can then copy it across and down.

This solution was taken from here and slightly altered to suit......
Unique list to be created from a column where an adjacent column has text cell values | Get Digital Help - Microsoft Excel resource

I hope that helps.

Good luck.

Ak
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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