Excel help please

Colbaker

Colbaker

Colbaker

Associate
Joined
8 Dec 2004
Posts
1,970
Location
Paignton, Devon
Hi all,

Having a little trouble with an Excel formula I hope you can help me with?

Say I have a column of city names I have visited, some city names are in it multiple times, in the next column I want to list all the citys I have visited, what would the equation be? I dont want to count the amount of times I have visited each city just list the citys I have visited?

I hope thats clear :)
 
Assuming you have headers in the first row, and your list of visited cities is in column A, use the following in cell B2 and copy it down

=INDEX($A$2:$A$200,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$200),0,0),0))
 
Meeko said:
Assuming you have headers in the first row, and your list of visited cities is in column A, use the following in cell B2 and copy it down

=INDEX($A$2:$A$200,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$200),0,0),0))
Click to expand...

When you say Headers what do you mean?
 
Meeko said:
Assuming you have headers in the first row, and your list of visited cities is in column A, use the following in cell B2 and copy it down

=INDEX($A$2:$A$200,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$200),0,0),0))
Click to expand...

It sort of worked, the problem is it has basically just made an exact copy of the city list, what im looking for it to do is only list the citys I have been too and not list how many times I have been to them, so for instance. ..

Column A
London
Madrid
New York

Cambridge
Phillidelphia

London
London
Madrid
New York
New York
London

Column B (The one with the formula)
London
Madrid
New York
Cambridge
Philkidelphia

Column B will actually be on another sheet to Column A but I have done it like this to make it easier to explain, Also notice there are blank cells in Column A, these are months that I have not visited a city but these spaces are not in Column B, how would I do that in an equation?


thanks for the help so far
 
Last edited:
You could try a Pivot Table on a separate sheet. This would extract only the cities. Set the table to auto refresh the data content when opening the file or manually refresh the data to see the additional cities when more are added to the list.
 
Sounds like when you do grouping in access. Not sure why you would want this in excel but I will have a think.

I would try this explanation in creating a list of unique and distinct values:

Select a blank cell, here I select C2, and enter this formula =IFERROR(INDEX($A$2:$A$8, MATCH(0, FREQUENCY(IF(EXACT($A$2:$A$8, TRANSPOSE($C$1:C1)), MATCH(ROW($A$2:$A$8), ROW($A$2:$A$8)), ""), MATCH(ROW($A$2:$A$8), ROW($A$2:$A$8))), 0)), "")
Click to expand...

$A$2:$A$8 is the list range of the values you want to extract from. $C$1:C1 is the first cell of the column you want to put out the extracting result), press Shift + Ctrl + Enter buttons, then drag fill handle to fill the range you need.
 
Last edited:
Th0nt said:
Sounds like when you do grouping in access. Not sure why you would want this in excel but I will have a think.

I would try this explanation in creating a list of unique and distinct values:



$A$2:$A$8 is the list range of the values you want to extract from. $C$1:C1 is the first cell of the column you want to put out the extracting result), press Shift + Ctrl + Enter buttons, then drag fill handle to fill the range you need.
Click to expand...

Hi, sorry only just got round to trying this and it does kind of work, only issue is when it hits an empty cell is $A$2 to $ A$8 the the rest of the dragged cells under column c show just 0's any idea what could be causing this?
 
Last edited:
Th0nt said:
Wow a 10 day break! :)

Why is the cell empty, can you provide a screenshot?
Click to expand...

:), it works now, as I thought I was doing it wrong.

I do have another quetion, if I were to add another column that i add wether i onky has a quick stop at said city, could i add something to your formula to remove these from the list? And also a column stating what hemisphere the city was in and only list the citys in the Northern hemisphere?

Thanks for your help so far :)
 
Yes, but my brain needs to process all my days activity and clear out toxins so will resume when I next read this. :)
 
You must log in or register to reply here.
Back
Top Bottom