VBA (or formula) to extract data from one row and convert to two rows

N

n0n0n0

New Member
Joined
Jul 13, 2017
Messages
19
In one sheet, I have an raw data like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Invoice Number[/TD]
[TD]Date[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]1st[/TD]
[TD][TABLE="width: 101"]
<tbody>[TR]
[TD="width: 101, align: right"][/TD]
[TD="width: 101, align: right"][/TD]
[TD="width: 101, align: right"]6086876869[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81, align: right"]13-Sep-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD="width: 100"]USD 755.17 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2nd[/TD]
[TD][TABLE="width: 101"]
<tbody>[TR]
[TD="width: 101, align: right"]6086453273[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81, align: right"]13-Sep-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD="width: 100"]USD 945.88 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

And on another sheet, I need to use the raw data above to make something like below:
[TABLE="width: 500"]
<tbody>[TR]
[TD]H[/TD]
[TD]USD[/TD]
[TD]2000[/TD]
[TD]copy 1st invoice number here[/TD]
[TD]IN[/TD]
[TD]copy 1st invoice datehere[/TD]
[TD]copy 1st invoice amount here
[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD]1022510000[/TD]
[TD]copy 1st invoice amount here[/TD]
[TD][/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]USD[/TD]
[TD]2000[/TD]
[TD]copy 2nd invoice number here[/TD]
[TD]IN[/TD]
[TD]copy 2nd invoice date here[/TD]
[TD]copy 2nd invoice amount here
[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD]1022510000[/TD]
[TD]copy 2nd invoice amount here[/TD]
[TD][/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


so from one record in raw data sheet and generate 2-row record in another sheet for about 100-150 records like that.

Please help! Thank you very much for your time and wisdom.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
Not enough information, what is the sheet name, where should the output go, what row does the data start on, is the raw data 3 columns or more? Does the raw data start in column M? What row does it start in, 7? Should output go next to it, in a new book, in a new sheet, upside down?

Imagine you're writing your problem to someone who can't see your PC screen.

Are you giving sufficient information so they can imagine the same screen you're seeing on your PC screen?

Quickest suggestion is record some code creating your 2 rows of data (for say 5 raw data rows) and post the resulting code here
 
Last edited:
Upvote 0
Details are a bit short but suppose your raw data is on Sheet1 as follows:


Book1
ABC
1Invoice NumberDateAmount
2608687686913-Sep-18USD 755.17
3608645327313-Sep-18USD 945.88
Sheet1


And Sheet2 exists with no data then try

Code: 
Sub Rearrange_Data()
  Dim a As Variant, b As Variant
  Dim i As Long
  
  With Sheets("Sheet1")
    a = .Range("A1", .Range("C" & .Rows.Count).End(xlUp)).Value
  End With
  ReDim b(1 To UBound(a) * 2, 1 To 7)
  For i = 2 To UBound(a)
    b(2 * i - 2, 1) = "H": b(2 * i - 2, 2) = Split(a(i, 3))(0): b(2 * i - 2, 3) = 2000: b(2 * i - 2, 4) = a(i, 1)
    b(2 * i - 2, 5) = "IN": b(2 * i - 2, 6) = a(i, 2): b(2 * i - 2, 7) = Val(Split(a(i, 3))(1))
    b(2 * i - 1, 1) = "T": b(2 * i - 1, 2) = 1022510000: b(2 * i - 1, 3) = b(2 * i - 2, 7): b(2 * i - 1, 5) = "N"
  Next i
  With Sheets("Sheet2").Range("A1:G1").Resize(UBound(b))
    .Value = b
    .Columns.AutoFit
  End With
End Sub

Result for me:


Book1
ABCDEFG
1
2HUSD20006086876869IN13/09/2018755.17
3T1022510000755.17N
4HUSD20006086453273IN13/09/2018945.88
5T1022510000945.88N
Sheet2
 
Upvote 0
Hi JackDanIce and Peter_SSs ,

Thank you for your prompt response and thank Peter_SSs for the solution. It works great. Since it was late last night and my brain was kinda stop functioning, i didn't post all the details.
Sheet1 is exactly like Peter_SSs assume with only 3 columns but could be more than 100 rows.
<table cellpadding="2.5px" rules="all" verdana,="" arial,="" tahoma,="" calibri,="" geneva,="" sans-serif;="" border-collapse:="" collapse;="" border-spacing:="" 0px;="" margin-bottom:="" 1em;="" color:="" rgb(51,="" 51,="" 51);="" border:="" 1px="" solid="" rgb(187,="" 187,="" 187);"="" width=""><colgroup><col><col><col><col></colgroup><thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="align: center"][/TH]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[/TR]
</thead><tbody>[TR]
[TD="align: center"]1[/TD]
[TD="align: right"]Invoice Number[/TD]
[TD="align: right"]Date[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]6086876869[/TD]
[TD="align: right"]13-Sep-18[/TD]
[TD]USD 755.17[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]6086453273[/TD]
[TD="align: right"]13-Sep-18[/TD]
[TD]USD 945.88[/TD]
[/TR]
</tbody></table>
Sheet1​


For each invoice above, the data in Sheet2 will have the following details:

<table class="wysiwyg_dashes" style="width: 500px;" width="500"><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[TD]AA[/TD]
[TD]AB[/TD]
[TD]AC[/TD]
[TD]AD[/TD]
[TD]AE[/TD]
[TD]AF[/TD]
[TD]AG[/TD]
[TD]AH[/TD]
[TD]AI[/TD]
[TD]AJ[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]H[/TD]
[TD]USD[/TD]
[TD]2000[/TD]
[TD]INVOICE #[/TD]
[TD][/TD]
[TD]IN[/TD]
[TD]INVOICE DATE[/TD]
[TD]INVOICE DATE[/TD]
[TD]INVOICE AMOUNT[/TD]
[TD]NET60[/TD]
[TD][/TD]
[TD]ACH[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]MONTH OF THE INVOICE DATE[/TD]
[TD]YEAR OF THE INVOICE DATE[/TD]
[TD]1020000000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]OPEN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ADD A SPACE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]T[/TD]
[TD]1022510000[/TD]
[TD]INVOICE AMOUNT[/TD]
[TD][/TD]
[TD][/TD]
[TD]NO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ADD A SPACE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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>
Cells with non-bold characters are the default values/texts for that cell regardless of record.
Cell with bold characters will have the data from sheet1 base on each record just like Peter_SSs did.
Blank cells will have to be blank without any character in it, no space either.

I'm following Peter_SSs 's script to adjust it to what I want, but I'm not so good with VBA so any tips or explanation of the script is much appreciated.

Thank you very much for your wisdom and time.
 
Upvote 0
I'm reposting the tables again.

Sheet1

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]INVOICE NUMBER[/TD]
[TD]INVOICE DATE[/TD]
[TD]INVOICE AMOUNT[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]6086876869[/TD]
[TD]13-Sep-18[/TD]
[TD]USD 755.17[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]6086453273[/TD]
[TD]13-Sep-18[/TD]
[TD]USD 945.88[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[TD]AA[/TD]
[TD]AB[/TD]
[TD]AC[/TD]
[TD]AD[/TD]
[TD]AE[/TD]
[TD]AF[/TD]
[TD]AG[/TD]
[TD]AH[/TD]
[TD]AI[/TD]
[TD]AJ[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]H[/TD]
[TD]USD[/TD]
[TD]2000[/TD]
[TD]INVOICE NUMBER[/TD]
[TD][/TD]
[TD]IN[/TD]
[TD]INVOICE DATE[/TD]
[TD]INVOICE DATE[/TD]
[TD]INVOICE AMOUNT[/TD]
[TD]NET60[/TD]
[TD][/TD]
[TD]ACH[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]MONTH OF INVOICE DATE[/TD]
[TD]YEAR OF INVOICE DATE[/TD]
[TD]10200000000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]OPEN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ADD A SPACE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]T[/TD]
[TD]1022510000[/TD]
[TD]INVOICE AMOUNT[/TD]
[TD][/TD]
[TD][/TD]
[TD]NO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ADD A SPACE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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]
Cells with non-bold characters are the default values/texts for that cell regardless of record.
Cell with bold characters will have the data from sheet1 base on each record just like Peter_SSs did.
Blank cells will have to be blank without any character in it, no space either.

I'm following Peter_SSs 's script to adjust it to what I want, but the script only add up to column G.
Sub Rearrange_Data() Dim a As Variant, b As Variant
Dim i As Long

With Sheets("Sheet1")
a = .Range("A1", .Range("C" & .Rows.Count).End(xlUp)).Value
End With
ReDim b(1 To UBound(a) * 2, 1 To 36)
For i = 2 To UBound(a)
b(2 * i - 2, 1) = "H": b(2 * i - 2, 2) = Split(a(i, 3))(0): b(2 * i - 2, 3) = 2000: b(2 * i - 2, 4) = a(i, 1)
b(2 * i - 2, 6) = "IN": b(2 * i - 2, 7) = a(i, 2): b(2 * i - 2, 8) = a(i, 2): b(2 * i - 2, 9) = Val(Split(a(i, 3))(1))
b(2 * i - 2, 10) = "NET60": b(2 * i - 2, 12) = "ACH": b(2 * i - 2, 18) = Month(a(i, 2)): b(2 * i - 2, 19) = Year(a(i, 2))
b(2 * i - 2, 20) = "1020000000": b(2 * i - 2, 30) = "OPEN": b(2 * i - 2, 36) = " "
b(2 * i - 1, 1) = "T": b(2 * i - 1, 2) = 1022510000: b(2 * i - 1, 3) = b(2 * i - 2, 9): b(2 * i - 1, 6) = "N": b(2 * i - 1, 17) = " "
Next i
With Sheets("Sheet2").Range("A1:G1").Resize(UBound(b))
.Value = b
.Columns.AutoFit
End With

End Sub
Click to expand...


Please review and advise. Thank you very much


 
Last edited:
Upvote 0
Hi @JackDanIce and @Peter_SSs, I was able to tweak the script to get them to split the data as needed. Thank you very much for the help.

Here is what I did:

Option Explicit

Sub Rearrange_Data()
Dim a As Variant, b As Variant
Dim i As Long

With Sheets("Sheet1")
a = .Range("A1", .Range("C" & .Rows.Count).End(xlUp)).Value
End With
ReDim b(1 To UBound(a) * 2, 1 To 36)
For i = 2 To UBound(a)
b(2 * i - 2, 1) = "H": b(2 * i - 2, 2) = Left(a(i, 3), 3): b(2 * i - 2, 3) = 2000: b(2 * i - 2, 4) = a(i, 1)
b(2 * i - 2, 6) = "IN": b(2 * i - 2, 7) = a(i, 2): b(2 * i - 2, 8) = a(i, 2): b(2 * i - 2, 9) = Right(a(i, 3), Len(a(i, 3)) - 4)
b(2 * i - 2, 10) = "NET60": b(2 * i - 2, 12) = "ACH": b(2 * i - 2, 18) = Month(a(i, 2)): b(2 * i - 2, 19) = Year(a(i, 2))
b(2 * i - 2, 20) = "1020000000": b(2 * i - 2, 30) = "OPEN": b(2 * i - 2, 36) = Space(1)
b(2 * i - 1, 1) = "T": b(2 * i - 1, 2) = 1022510000: b(2 * i - 1, 3) = b(2 * i - 2, 9): b(2 * i - 1, 6) = "N": b(2 * i - 1, 17) = Space(1)
Next i
With Sheets("Sheet2").Range("A1:AJ1").Resize(UBound(b))
.Value = b
.Columns.AutoFit
End With
End Sub
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Copying Data from Sheet 1 to Sheet 2 and adding rows- Code Needed
2 3 4
Replies
30
Views
1K
mumps
mumps
A
  • Question Question
Inputting raw data from rows and extracting results from temporary formula result into respective rows
Replies
0
Views
443
Andre3
A
K
  • Solved
VBA to copy column/data from two .csv files to one combined file.
Replies
6
Views
1K
kbilly70
K
P
  • Question Question
ListBox Click to textbox & delete row
Replies
0
Views
607
pure vito
P
willow1985
  • Solved
VBA copy set amount of rows into forms until end of data set
2
Replies
15
Views
704
willow1985
willow1985

Forum statistics

Threads
1,223,714
Messages
6,174,052
Members
452,542
Latest member
Bricklin

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