Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating 1 row from n rows using concatenate and if then statement
Hello Excel gurus,
As you will see, I am relatively new at this. Using code found on this forum and other websites, I have developed a macro that will loop through three columns looking for a row of unique items. I am now having difficulty concatenating the data from other cells in those combined rows, into one cell. For example, below is the type of setup the spreadsheet comes in. City State Country Amount Restaurant Type Philadelphia Pennsylvania United States 12 Chinese Philadelphia Pennsylvania United States 19 Italian Philadelphia Pennsylvania United States 15 Fast Food Detroit Michigan United States 7 Chinese Detroit Michigan United States 10 Italian This is what I need to see. City State Country Restaurants Philadelphia Pennsylvania United States Chinese-12; Italian-19; Fast Food-15 Detroit Michigan United States Chinese-7; Italian-10 This is the code I pulled together to look at the unique items. Any help you can provide would be appreciated. ------------------------------------------------ Sub ForMapping() Dim MapArray() Dim MapEndRow As Integer Dim FlowWorkbook As Workbook Set FlowWorkbook = Workbooks("Flowchart.xls") MapEndRow = FlowWorkbook.Sheets("DATA").Range("A1").End(xlDown ).Row FlowWorkbook.Activate ReDim MapArray(1 To MapEndRow - 1) For x = 2 To MapEndRow MapArray(x - 1) = FlowWorkbook.Sheets("DATA").Cells(x, 2).Value & _ "_1_" & FlowWorkbook.Sheets("DATA").Cells(x, 3).Value & "_2_" & _ FlowWorkbook.Sheets("DATA").Cells(x, 4).Value Next x UniqueMap = UniqueItems(MapArray(), False) QUICK_SORT UniqueMap, 1, UBound(UniqueMap) For x = 1 To UBound(UniqueMap) tempval2 = InStr(1, UniqueMap(x), "_1_") tempval3 = InStr(1, UniqueMap(x), "_2_") FlowWorkbook.Sheets("MapData").Cells(x + 1, 2).Value = _ Left(UniqueMap(x), tempval2 - 1) FlowWorkbook.Sheets("MapData").Cells(x + 1, 3).Value = _ Mid(UniqueMap(x), tempval2 + 3, ((tempval3) - (tempval2 + 3))) FlowWorkbook.Sheets("MapData").Cells(x + 1, 4).Value = _ Mid(UniqueMap(x), tempval3 + 3) Next x End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating an Oracle UPDATE Statement from Excel rows | Excel Discussion (Misc queries) | |||
Creating a list / concatenate function | Excel Worksheet Functions | |||
Include date in concatenate statement | Excel Discussion (Misc queries) | |||
How do I change font attributes in a Concatenate statement? | Excel Worksheet Functions | |||
If Statement and Concatenate | Excel Discussion (Misc queries) |