Hi -
I would like to convert a multiple columns into a linear table. It is possible to add new columns to the right or add new items to each list. How can I make a macro, that will able to list down items from the table (column A-E) to linear list (column G-I).
Could someone help me or give me a reference code for this? Attached sample file.
Thank you.
You can use PowerQuery, the free excel add on to do this without VBA.
http://datapigtechnologies.com/blog/...data-explorer/
Please ensure you mark your thread as Solved once it is. Click here to see how.
If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.
I need a macro vba..Originally Posted by mikeTRON
it was different set, i already saw codes like that what I need is different, take a closer look on the list, per column is a different list, that needs to be consolidated in one list.
Try this for Results starting "G2".
Regards Mick
Please Login or Register to view this content.
Try this also:
PHP Code:Sub sd()
Dim ArrP1(), ArrP2(), List1(), List2(), a As Variant
Dim sArr, dArr As String
ReDim ArrP1(1 To 4, 1 To 5)
ReDim ArrP2(1 To 4, 1 To 5)
Dim i, j, k
[G3:I100].ClearContents
ArrP1 = Range("A4:E7")
ArrP2 = Range("A11:E14")
For j = 1 To 5
For i = 1 To 4
If ArrP1(i, j) = "" Then Exit For
sArr = ArrP1(i, j) & "|"
dArr = dArr & sArr
k = Len(dArr) - Len(Replace(dArr, "|", ""))
[H3].Offset(k - 1, 0).Value = Cells(3, j)
[I3].Offset(k - 1, 0).Value = ArrP1(i, j)
[G3].Offset(k - 1, 0).Value = [A2].Value
Next i
Next j
For j = 1 To 5
For i = 1 To 4
If ArrP2(i, j) = "" Then Exit For
sArr = ArrP2(i, j) & "|"
dArr = dArr & sArr
k = Len(dArr) - Len(Replace(dArr, "|", ""))
[H3].Offset(k - 1, 0).Value = Cells(10, j)
[I3].Offset(k - 1, 0).Value = ArrP2(i, j)
[G3].Offset(k - 1, 0).Value = [A9].Value
Next i
Next j
End Sub
Quang PT
Im getting an error in "Lst = UsedRange.Rows.Count", and is not updating. Thanks.Try this for Results starting "G2".
Regards MickPlease Login or Register to view this content.
The solution is easy.
Please Login or Register to view this content.
Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
thank you it is now working.The solution is easy.
Please Login or Register to view this content.
Good catch bakerman2.
SBBmaster09 ,you're welcome
Hi would like to update this. How can I use the code when the data is from other workbookInput and would like to list it in workbookOutput?
Try this for Results starting "G2".
Regards MickPlease Login or Register to view this content.
Hi -
How would I able to use this code if my column A-E is from workbookInput and column G-I should be to workbookOutput? I'm not that yet good in vba, apologize for the inconvenience.
Thank you.
I tried the code you have given with my template, just got something, this line Left(R.Offset(, Ac).value, 4) = "List" means containing "List" value right? What if my header doesn't have the same value? It has different naming convention on headers. what should be the formula for this? thanks.
Are those Names, workbook names or sheet names, if the former what are the sheet names.
How would I able to use this code if my column A-E is from workbookInput and column G-I should be to workbookOutput? I'm not that yet good in vba, apologize for the inconvenience.
In my WorkbookInput sheetname is "BS" where the multiple columns(table) is, this is my source data. the WorkbookOutput sheetname "BS" is where I need to translate the columns to a linear list, The headers which are in the row 3 and 10 of the attached previous file, has different texts. It should not be just equal to "List". Row 10 can be possible to change since the items from the columns starting from row 3 is numerous.
I hope I have explained it, thanks so much for the help.
Hi -
I able to tweak the code for what I need, sharing this file and added comments for reference.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks