#1
|
|||
|
|||
Concatenated data in subsequent rows
We would like to list concatenated city names and services. I am trying to have it so we can use a VLOOKUP-type function where if we type in a city name, that city's concatenated data appears in another row. Here's the kicker - we would like to be able to type in other city name in that same original cell and have that new city's concatenated data appear under the first city's data. It would look something like this:
SERVICE1CITYNAME1 SERVICE2CITYNAME1 SERVICE3CITYNAME1 SERVICE4CITYNAME1 SERVICE1CITYNAME2 SERVICE2CITYNAME2 SERVICE3CITYNAME2 SERVICE4CITYNAME2 SERVICE1CITYNAME3 SERVICE2CITYNAME3 SERVICE3CITYNAME3 SERVICE4CITYNAME3 Etc. Any help would be greatly appreciated, as I am under the gun here! |
#2
|
|||
|
|||
try this macro: (same in file attached):
Sub concatenate() Dim nextRow As Long With Sheets("Sheet1") nextRow = Range("G" & Rows.Count).End(xlUp).Row + 1 Cells(nextRow, "G").Value = Cells(2, "A").Value & Cells(5, "D").Value Cells(nextRow + 1, "G").Value = Cells(3, "A").Value & Cells(5, "D").Value Cells(nextRow + 2, "G").Value = Cells(4, "A").Value & Cells(5, "D").Value Cells(nextRow + 3, "G").Value = Cells(5, "A").Value & Cells(5, "D").Value MsgBox "Mission accomplished! " End With End Sub |
#3
|
|||
|
|||
Catalin.B - thank you! If I manually enter a city name in the yellow box rather than select from the drop down, I do get an error message. However, it's just an extra click to go through to get the desired effect. Thanks again! That did the trick!
|
#4
|
|||
|
|||
Spoke too soon...
My boss just notified me of the following:
I need up to 5 input lines for the cities, not just one. I don’t want to have to type the City name and press “Start” after each city name, though the way it appends in a list at the bottom is good – the outcome is right, the process almost there. I want to put in ALL city names, then press start ONCE, and then have it produce the entire result. Any suggestions on how I could do that? |
#5
|
|||
|
|||
cell D5 was formatted with data validation to allow only Cities from column B to appear in drop down list... and it was set to give you a message if you enter a city that is not in column B list...
Code:
Sub concatenate() Dim nextRow As Long, i As Integer With Sheets("Sheet1") For i = 5 To 9 nextRow = Range("G" & Rows.Count).End(xlUp).Row + 1 Cells(nextRow, "G").Value = Cells(2, "A").Value & Cells(i, "D").Value Cells(nextRow + 1, "G").Value = Cells(3, "A").Value & Cells(i, "D").Value Cells(nextRow + 2, "G").Value = Cells(4, "A").Value & Cells(i, "D").Value Cells(nextRow + 3, "G").Value = Cells(5, "A").Value & Cells(i, "D").Value Next i MsgBox "Mission accomplished! :)" End With End Sub Code:
For i = 5 To 9 Code:
For i = 5 To 100 |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Odd behaviour of table - keep rows together | Stephan Lindner | Word Tables | 1 | 09-10-2011 05:31 AM |
Product and Sum on 2 rows | apolloman | Excel | 2 | 04-27-2011 07:14 AM |
Rows to Columns | kyakobi | Excel | 5 | 12-07-2010 03:12 PM |
Average of many rows | speedycorn1 | Excel | 1 | 10-30-2010 07:54 PM |
How do I turn the top 2 rows into a header | GR8Fandini | Excel | 1 | 02-06-2010 12:58 PM |