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

[n0n0n0]

In one sheet, I have an raw data like this:

	Invoice Number	Date	Amount
1st	6086876869 <tbody> </tbody>	13-Sep-18 <tbody> </tbody>	USD 755.17 <tbody> </tbody>
2nd	6086453273 <tbody> </tbody>	13-Sep-18 <tbody> </tbody>	USD 945.88 <tbody> </tbody>

<tbody>
</tbody>

And on another sheet, I need to use the raw data above to make something like below:

H	USD	2000	copy 1st invoice number here	IN	copy 1st invoice datehere	copy 1st invoice amount here
T	1022510000	copy 1st invoice amount here		N
H	USD	2000	copy 2nd invoice number here	IN	copy 2nd invoice date here	copy 2nd invoice amount here
T	1022510000	copy 2nd invoice amount here		N

<tbody>
</tbody>

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.

 

[JackDanIce]

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

 

[Peter_SSs]

Details are a bit short but suppose your raw data is on Sheet1 as follows:

Book1
	A	B	C
1	Invoice Number	Date	Amount
2	6086876869	13-Sep-18	USD 755.17
3	6086453273	13-Sep-18	USD 945.88
Sheet1

And Sheet2 exists with no data then try

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
	A	B	C	D	E	F	G
1
2	H	USD	2000	6086876869	IN	13/09/2018	755.17
3	T	1022510000	755.17		N
4	H	USD	2000	6086453273	IN	13/09/2018	945.88
5	T	1022510000	945.88		N
Sheet2

 

[n0n0n0]

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.

 

[n0n0n0]

It seems that the table I provided in previous post did not show properly. I'm not sure why.

 

[n0n0n0]

I'm reposting the tables again.

Sheet1

	A	B	C
1	INVOICE NUMBER	INVOICE DATE	INVOICE AMOUNT
2	6086876869	13-Sep-18	USD 755.17
3	6086453273	13-Sep-18	USD 945.88

<tbody>
</tbody>

Sheet2

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

1

H

USD

2000

INVOICE NUMBER

IN

INVOICE DATE

INVOICE DATE

INVOICE AMOUNT

NET60

ACH

MONTH OF INVOICE DATE

YEAR OF INVOICE DATE

10200000000

OPEN

ADD A SPACE

2

T

1022510000

INVOICE AMOUNT

NO

ADD A SPACE

<tbody>
</tbody>

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

Please review and advise. Thank you very much

 

[n0n0n0]

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

 

[Peter_SSs]

Glad you were able to get something that worked for you.