If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Can I use vlookup to do this?
I have a sheet like below:
aaa 1 bbb 2 bbb 3 ccc 4 ddd 5 ddd 6 ddd 7 Now I want to reorganize the table like below: aaa 1 bbb 2,3 ccc 4 ddd 5,6,7 What should I do? Thanks a lot! |
#2
|
|||
|
|||
Can I use vlookup to do this?
Try this UDF (User Defined function). From workbook launch VBE using Alt+F11.
From menu Insert a Module and paste the below function.Close and get back to workbook and try the below formula. Syntax: =VLOOKUP_CONCAT(rngRange,strLookupValue,intColumn, strDelimiter) rngRange is the Range strLookupValue is the lookup string or cell reference inColumn is the column to be concatenated strDelimiter Optional . Default is space Examples: '1. To vlookup 'jacob' and concatenate all entries of 2nd column =VLOOKUP_CONCAT(A1:B10,"jacob",2) '2. with lookup value in cell C1 =VLOOKUP_CONCAT(A1:B10,C1,2) '3. with delimiter as comma =VLOOKUP_CONCAT(A1:B10,C1,2,",") Function VLOOKUP_CONCAT(rngRange As Range, _ strLookupValue As String, intColumn As Integer, _ Optional strDelimiter As String = " ") Dim lngRow As Long For lngRow = 1 To rngRange.Rows.Count If CStr(rngRange(lngRow, 1)) = strLookupValue Then _ VLOOKUP_CONCAT = VLOOKUP_CONCAT & strDelimiter & _ rngRange(lngRow, intColumn) Next VLOOKUP_CONCAT = Mid(VLOOKUP_CONCAT, 2) End Function If this post helps click Yes --------------- Jacob Skaria "Coeus" wrote: I have a sheet like below: aaa 1 bbb 2 bbb 3 ccc 4 ddd 5 ddd 6 ddd 7 Now I want to reorganize the table like below: aaa 1 bbb 2,3 ccc 4 ddd 5,6,7 What should I do? Thanks a lot! |
#3
|
|||
|
|||
Can I use vlookup to do this?
Thank you a lot! That's really helpful!
"Jacob Skaria" wrote: Try this UDF (User Defined function). From workbook launch VBE using Alt+F11. From menu Insert a Module and paste the below function.Close and get back to workbook and try the below formula. Syntax: =VLOOKUP_CONCAT(rngRange,strLookupValue,intColumn, strDelimiter) rngRange is the Range strLookupValue is the lookup string or cell reference inColumn is the column to be concatenated strDelimiter Optional . Default is space Examples: '1. To vlookup 'jacob' and concatenate all entries of 2nd column =VLOOKUP_CONCAT(A1:B10,"jacob",2) '2. with lookup value in cell C1 =VLOOKUP_CONCAT(A1:B10,C1,2) '3. with delimiter as comma =VLOOKUP_CONCAT(A1:B10,C1,2,",") Function VLOOKUP_CONCAT(rngRange As Range, _ strLookupValue As String, intColumn As Integer, _ Optional strDelimiter As String = " ") Dim lngRow As Long For lngRow = 1 To rngRange.Rows.Count If CStr(rngRange(lngRow, 1)) = strLookupValue Then _ VLOOKUP_CONCAT = VLOOKUP_CONCAT & strDelimiter & _ rngRange(lngRow, intColumn) Next VLOOKUP_CONCAT = Mid(VLOOKUP_CONCAT, 2) End Function If this post helps click Yes --------------- Jacob Skaria "Coeus" wrote: I have a sheet like below: aaa 1 bbb 2 bbb 3 ccc 4 ddd 5 ddd 6 ddd 7 Now I want to reorganize the table like below: aaa 1 bbb 2,3 ccc 4 ddd 5,6,7 What should I do? Thanks a lot! |
#4
|
|||
|
|||
Can I use vlookup to do this?
Hi Jacob
I tried copying into module sheet, it is throwing up an error "Jacob Skaria" wrote: Try this UDF (User Defined function). From workbook launch VBE using Alt+F11. From menu Insert a Module and paste the below function.Close and get back to workbook and try the below formula. Syntax: =VLOOKUP_CONCAT(rngRange,strLookupValue,intColumn, strDelimiter) rngRange is the Range strLookupValue is the lookup string or cell reference inColumn is the column to be concatenated strDelimiter Optional . Default is space Examples: '1. To vlookup 'jacob' and concatenate all entries of 2nd column =VLOOKUP_CONCAT(A1:B10,"jacob",2) '2. with lookup value in cell C1 =VLOOKUP_CONCAT(A1:B10,C1,2) '3. with delimiter as comma =VLOOKUP_CONCAT(A1:B10,C1,2,",") Function VLOOKUP_CONCAT(rngRange As Range, _ strLookupValue As String, intColumn As Integer, _ Optional strDelimiter As String = " ") Dim lngRow As Long For lngRow = 1 To rngRange.Rows.Count If CStr(rngRange(lngRow, 1)) = strLookupValue Then _ VLOOKUP_CONCAT = VLOOKUP_CONCAT & strDelimiter & _ rngRange(lngRow, intColumn) Next VLOOKUP_CONCAT = Mid(VLOOKUP_CONCAT, 2) End Function If this post helps click Yes --------------- Jacob Skaria "Coeus" wrote: I have a sheet like below: aaa 1 bbb 2 bbb 3 ccc 4 ddd 5 ddd 6 ddd 7 Now I want to reorganize the table like below: aaa 1 bbb 2,3 ccc 4 ddd 5,6,7 What should I do? Thanks a lot! |
#5
|
|||
|
|||
Can I use vlookup to do this?
Copy only the Function..
Function VLOOKUP_CONCAT(rngRange As Range, _ strLookupValue As String, intColumn As Integer, _ Optional strDelimiter As String = " ") Dim lngRow As Long For lngRow = 1 To rngRange.Rows.Count If CStr(rngRange(lngRow, 1)) = strLookupValue Then _ VLOOKUP_CONCAT = VLOOKUP_CONCAT & strDelimiter & _ rngRange(lngRow, intColumn) Next VLOOKUP_CONCAT = Mid(VLOOKUP_CONCAT, 2) End Function If this post helps click Yes --------------- Jacob Skaria "Nadeem" wrote: Hi Jacob I tried copying into module sheet, it is throwing up an error "Jacob Skaria" wrote: Try this UDF (User Defined function). From workbook launch VBE using Alt+F11. From menu Insert a Module and paste the below function.Close and get back to workbook and try the below formula. Syntax: =VLOOKUP_CONCAT(rngRange,strLookupValue,intColumn, strDelimiter) rngRange is the Range strLookupValue is the lookup string or cell reference inColumn is the column to be concatenated strDelimiter Optional . Default is space Examples: '1. To vlookup 'jacob' and concatenate all entries of 2nd column =VLOOKUP_CONCAT(A1:B10,"jacob",2) '2. with lookup value in cell C1 =VLOOKUP_CONCAT(A1:B10,C1,2) '3. with delimiter as comma =VLOOKUP_CONCAT(A1:B10,C1,2,",") Function VLOOKUP_CONCAT(rngRange As Range, _ strLookupValue As String, intColumn As Integer, _ Optional strDelimiter As String = " ") Dim lngRow As Long For lngRow = 1 To rngRange.Rows.Count If CStr(rngRange(lngRow, 1)) = strLookupValue Then _ VLOOKUP_CONCAT = VLOOKUP_CONCAT & strDelimiter & _ rngRange(lngRow, intColumn) Next VLOOKUP_CONCAT = Mid(VLOOKUP_CONCAT, 2) End Function If this post helps click Yes --------------- Jacob Skaria "Coeus" wrote: I have a sheet like below: aaa 1 bbb 2 bbb 3 ccc 4 ddd 5 ddd 6 ddd 7 Now I want to reorganize the table like below: aaa 1 bbb 2,3 ccc 4 ddd 5,6,7 What should I do? Thanks a lot! |
Thread Tools | |
Display Modes | |
|
|