VBA Transpose from column to Row

K

Krazy

New Member
Joined
Feb 16, 2016
Messages
8
Hi all,

Thank you in advance for helping.

I am very new for VBA and have some data would like to transpose from column to row where it is dynamic, see below, please -

[TABLE="width: 154"]
<colgroup><col style="width:58pt" span="2" width="77"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 77"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 77, align: right"]Item
[/TD]
[TD="class: xl64, width: 77"]- Value
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 77, align: right"]1
[/TD]
[TD="class: xl64, width: 77"]D
[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]2
[/TD]
[TD="class: xl64"]A
[/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl64"]B
[/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl64"]C
[/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl64"]D
[/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl64"]E
[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]3[/TD]
[TD="class: xl64"]C
[/TD]
[/TR]
</tbody>[/TABLE]

To Make it like below

Item Whitelist Values
1 D
2 A B C D E
3 C

I have searched the forum for quite long, but only could found those transpose from row to column.:confused:

After transposed, I would like to use VBA to validate data from other range,

say, if the cell "A1" equals 2 in other range, then VBA to help to check the "white list's value" of 2, thus, the cell "B1" could be either A/B/C/D/E, mark "Value in range", else mark "Value not in range" in "C1"

As below -

A B C
2 D "

If the item is not on the list, mark "Item not found" in "C1"

A B C
4 A "Item not found"


Grateful if anyone could help, please. Thank you very much.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Krazy,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?


I found your instructions a little confusing.

Here is a macro for you to consider, based on your flat text display.

You can change the raw data worksheet name in the macro.

Sample raw data, and, results:


Excel 2007
ABCDEF
1Item- ValueItemWhitelist Values
21D1D
32A2A B C D E
4B3C
5C4Value not in range
6D
7E
83C
94
10
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Sub ReorgData()
' hiker95, 02/16/2016, ME921710
Dim a As Variant, lr As Long, r As Long, nr As Long, n As Long
Application.ScreenUpdating = False
With Sheets("Sheet1")   '<-- you can change the sheet name here
  lr = .Columns("A:B").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
  a = .Range("A1:B" & lr)
  With .Range("A2:A" & lr)
    .SpecialCells(xlCellTypeBlanks).Formula = "=R[-1]C"
    .Value = .Value
  End With
  .Range("A2:B" & lr).Sort key1:=.Range("A2"), order1:=1, key2:=.Range("B2"), order2:=1
  .Columns("E:F").ClearContents
  .Range("E1").Resize(, 2).Value = Array("Item", "Whitelist Values")
  nr = 1
  For r = 2 To lr
    n = Application.CountIf(.Columns(1), .Cells(r, 1).Value)
    If n = 1 Then
      nr = nr + 1
      .Cells(nr, 5).Value = .Cells(r, 1).Value
      If Not .Cells(r, 2) = vbEmpty Then
        .Cells(nr, 6).Value = .Cells(r, 2).Value
      Else
        .Cells(nr, 6).Value = "Value not in range"
      End If
    ElseIf n > 1 Then
      nr = nr + 1
      .Cells(nr, 5).Value = .Cells(r, 1).Value
      .Cells(nr, 6).Value = Join(Application.Transpose(Range("B" & r & ":B" & (r + n) - 1)), " ")
    End If
    r = r + n - 1
  Next r
  .Range("A1:B" & lr) = a
  .UsedRange.Columns.AutoFit
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorgData macro.
 
Last edited:
Upvote 0
Thanks a lot for the prompt reply and sorry for the confusion caused.

I am using Windows 7 with Excel 2010 and 2013.

Let me try to make it clear -

I am creating a database for data validation, not sure whether it is necessary for data validation to make those values transposed. I've tried your code, can it be making those values of item one cell each rather than one cell to include all the values?

After creating the database, I need to use those transposed data to verify new collected raw data, say from another worksheet, then I wish to run the code to check whether those values of the items within my whitelist.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TABLE="class: grid, width: 1090"]
<colgroup><col><col><col span="2"><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]item
[/TD]
[TD]value[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Item[/TD]
[TD]Whitelist Values
[/TD]
[TD] [/TD]
[TD]Raw Data (item)[/TD]
[TD]value[/TD]
[TD]Matching Result[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]D[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD]D[/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD]E[/TD]
[TD] [/TD]
[TD]<--not match the values on my whitelist, show "error message"[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]A[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[TD]A B C D[/TD]
[TD]
[/TD]
[TD="align: right"]1[/TD]
[TD]D[/TD]
[TD] [/TD]
[TD]<--match the values on my whitelist, show "match"[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]B[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]3[/TD]
[TD]D[/TD]
[TD] [/TD]
[TD="align: right"]5[/TD]
[TD]A[/TD]
[TD] [/TD]
[TD]<--item not in my whitelist, show "new item & value"[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]C[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]4[/TD]
[TD]E[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]D[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]D[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]E[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
</tbody>[/TABLE]
 
Upvote 0
Don't know why I can't paste my excel table here..

[TABLE="class: grid, width: 500, align: center"]
<colgroup><col style="mso-width-source:userset;mso-width-alt:1316;width:27pt" width="36"> <col style="mso-width-source:userset;mso-width-alt:1499;width:31pt" width="41"> <col style="width:48pt" span="2" width="64"> <col style="mso-width-source:userset;mso-width-alt:1316;width:27pt" width="36"> <col style="mso-width-source:userset;mso-width-alt:4022;width:83pt" width="110"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:3766;width:77pt" width="103"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:3730;width:77pt" width="102"> <col style="mso-width-source:userset;mso-width-alt:14848;width:305pt" width="406"> </colgroup><tbody>[TR]
[TD="width: 36"]item
[/TD]
[TD="width: 41"]value[/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[TD="width: 36"]Item
[/TD]
[TD="width: 110"]Whitelist Values[/TD]
[TD="width: 64"] [/TD]
[TD="width: 103"]Raw Data (item)[/TD]
[TD="width: 64"]value[/TD]
[TD="width: 102"]Matching Result[/TD]
[TD="width: 406"] [/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD]D[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD]D
[/TD]
[TD]
[/TD]
[TD="align: right"]1[/TD]
[TD]E
[/TD]
[TD] [/TD]
[TD]<--not match the values on my whitelist, show "error message"
[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]A[/TD]
[TD] [/TD]
[TD]
[/TD]
[TD="align: right"]2[/TD]
[TD]A B C D[/TD]
[TD]
[/TD]
[TD="align: right"]1[/TD]
[TD]D[/TD]
[TD]
[/TD]
[TD]<--match the values on my whitelist, show "match"[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]B[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]3[/TD]
[TD]D[/TD]
[TD] [/TD]
[TD="align: right"]5[/TD]
[TD]A[/TD]
[TD] [/TD]
[TD]<--item not in my whitelist, show "new item & value"[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]C[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]4[/TD]
[TD]E[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]D[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]D[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]E[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Normally, I use match and vlookup to check, but I can't get it work for those with several whitelist values, like item 2, it could be A/B/C/D, match seems could not handle this, and I don't want to write a lot of "or" if I could transpose those values of item 2 into one cell each in the same row.

=MATCH(I5,(VLOOKUP(H5,E:F,2,FALSE)),0)

Thank you again for your help.
 
Upvote 0
Krazy,

I do not understand what you are trying to do from your reply #3, #4, and, #5.

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Q
  • Question Question
Macro needed to insert 4 rows in table, copy data from above, copy data from another sheet and paste special (transpose values) to those new rows...re
Replies
1
Views
229
sijpie
sijpie
N
  • Question Question
VBA Transpose & Join Help
Replies
5
Views
330
Alex Blakenburg
A
C
  • Question Question
Send e-mails selected from a list in excel
Replies
1
Views
150
Sequoyah
Sequoyah
C
  • Question Question
Macro Help - Transposing Some Data from Columns to Rows, Accounting for Duplicate Data
Replies
1
Views
97
Kaper
Kaper
excelbytes
  • Solved
Data Validation Show Codes Only
2 3
Replies
20
Views
357
HighAndWilder
H

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top