How to get get cell valure based on parameters

Bpuad

New Member
Joined
Feb 17, 2012
Messages
28
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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
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
 

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
Hi,

I'm pleased it worked for you, thanks for the feedback. :biggrin:

Ak
 

Watch MrExcel Video

Forum statistics

Threads
1,102,597
Messages
5,487,765
Members
407,610
Latest member
bellakim00

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top