Listing unique values, based on matching two other columns in a single cell

**bsamson05** · 02-17-2016, 12:11 PM

I have the following info in an IMPORT sheet:

Date	        route	City
2/1/2016	Albany	Latham
2/1/2016	Albany	Loudonville
2/1/2016	Albany	Albany
2/2/2016	Albany	Schenectady
2/2/2016	Albany	Voorheesville
2/2/2016	Albany	Schenectady
2/2/2016	Albany	Schenectady
2/2/2016	Albany	Schenectady
2/2/2016	Albany	Schenectady
2/2/2016	Albany	Hagaman
2/2/2016	Albany	Schenectady
2/2/2016	Albany	Latham
2/2/2016	Albany	Latham
2/3/2016	Albany	Chatham
2/3/2016	Albany	Hudson
2/3/2016	Albany	Catskill
2/3/2016	Albany	Schenectady

I have searched and have been unable to find a formula or some means to list the unique cities, based on matching the route and date - in a cell, separated by commas.

I am very sorry if the answer exists, but I have not found a solution in searching these forums or google.

I have another sheet with the following info and am looking to get the info in RED and listed like this:

A              B             C
Date	        Truck	       Areas Serviced
2/1/2016	Albany      Albany, Latham, Loudonville

Thank you in advance for any direction!

**Czeslaw** · 02-17-2016, 02:18 PM

Maybe a suitable option.

**José Augusto** · 02-17-2016, 02:51 PM

Hi
I suppose that you must use VBA.
Try these code in a module

Option Explicit
Function Areas_Served(data As Range, Date_Sel As Date, Truck As String) As String
Dim x As String, i As Long, j As Long
    x = ""
    For i = 1 To data.Rows.Count
        If data(i, 1) = Date_Sel And data(i, 2) = Truck Then
            x = data(i, 3) & ", " & x
        End If
    Next i
    Areas_Served = IIf(Len(x) > 1, Left(x, Len(x) - 2), x)
End Function

If your data without labels is in range ($A$2:$C$18), Date_Sel in F2 and Truck in G2 , use =Areas_Served(A2:C18,F2,G2)
See the file Areas_Served.xlsm

**hemesh** · 02-17-2016, 02:59 PM

you could use pivot table as a option but if you need a formula solution then follow post#3.

One more formula option requires VBA follow below post
http://www.excelforum.com/tips-and-t...geravatar.html
and copy paste the code as suggested in above link and then
in sheet 2 in C2 copy paste below then hold control and shift together and then hit enter to make it array formula

=ConcatAll(IF((A2=Sheet1!$A$1:$A$17)*(B2=Sheet1!$B$1:$B$17),Sheet1!$C$1:$C$17,""),",")

change the ranges according to your data set

**bsamson05** · 02-17-2016, 03:36 PM

I made it work ... thank you so much Czeslaw!!!

Listing unique values, based on matching two other columns in a single cell

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Listing unique values, based on matching two other columns in a single cell

Re: Listing unique values, based on matching two other columns in a single cell

Re: Listing unique values, based on matching two other columns in a single cell

Re: Listing unique values, based on matching two other columns in a single cell

Re: Listing unique values, based on matching two other columns in a single cell

Thread Information

Users Browsing this Thread

Similar Threads

how to collate list of unique values from multiple columns into a single column

Listing many unique values based on an if statement

VLookup/Listing Matching Values

Listing values into one cell based on a status...

Copying a row based on matching unique values

Count unique values based on matching criteria

[SOLVED] listing values to choose from based on value in another cell

Bookmarks

Bookmarks

Posting Permissions