Copy rows to row above without a unique value to match with

[spectraflame]

I have a text file that is awful. It has the data broken into 3 different rows without any unique identifier in the 2nd and 3rd row. The goal is to be able to parse the text file into usable columns with data for each record on a single row. My thought is I would first need to import the raw text file, without delimiting anything, into Excel and remove the blank rows. Then have a macro to copy the raw data from rows 2 and 3 into cells B & C of row one and then do this for the entire sheet. Are there other tools within Excel that would help with transferring this data to a single row or is VBA the best approach?

 

[alansidman]

Suggest you upload with XL2BB (no Pictures) a brief sample of what your data looks like and then for that data show what you would like it to look like. 8-12 records only

 

[spectraflame]

Rows 1 - 12 is the raw CSV file imported with the blank rows removed ( I also had to remove personal data where the X X X X are displayed for posting purposes)
Rows 15-18 show the data copied to 3 columns.

I feel that once the data is in a single row, I can then further parse the data into more usable columns.

Cycle 02 Results 06.30.23 sds.txt
	A	B	C
1	CUS0200011500100030817003 IDLE X X X X 10290 0000000012083901 000N 0000000012083901
2	MTR02000115001000000000000000 I 000136305273000011500045115000450000010000NN NNG 000 0000000000
3	RDG02000115NGAS 000000000K00000100000000000000106272023140301N000000000000U11 000000400K 00000010020000000000
4	CUS0200011500100028540020 X X X X 0000000011529202 000N 0000000011529202
5	MTR02000115001000000000000000 A 000096037758000011500090115000900000020003NN NNG 000 0000000000
6	RDG02000115NGAS 000000000K00000100000003776000406282023101018N000400000010U11 327670400K 00000010020000003763
7	CUS0200011500100040916002 X X X X 0000000011471002 000N 0000000011471002
8	MTR02000115001000000000000000 A 000096107417000011500091115000910030020003NN NNG 000 0000000000
9	RDG02000115NGAS 000000000K00000100000002822000306272023142139N000000000000U11 007030400K 00000010020000002809
10	CUS0200011500100005143018 X X X X 0000000014264701 000N 0000000014264701
11	MTR02000115001000000000000000 A 000207246988000011500145115001450000020000NN NNG 000 0000000000
12	RDG02000115NGAS 000000000K00000100000000415000206272023140956N000400000010U11 004150400K 00000010140000000413
13
14
15	CUS0200011500100030817003 IDLE X X X X 10290 0000000012083901 000N 0000000012083901	MTR02000115001000000000000000 I 000136305273000011500045115000450000010000NN NNG 000 0000000000	RDG02000115NGAS 000000000K00000100000000000000106272023140301N000000000000U11 000000400K 00000010020000000000
16	CUS0200011500100028540020 X X X X 0000000011529202 000N 0000000011529202	MTR02000115001000000000000000 A 000096037758000011500090115000900000020003NN NNG 000 0000000000	RDG02000115NGAS 000000000K00000100000003776000406282023101018N000400000010U11 327670400K 00000010020000003763
17	CUS0200011500100040916002 X X X X 0000000011471002 000N 0000000011471002	MTR02000115001000000000000000 A 000096107417000011500091115000910030020003NN NNG 000 0000000000	RDG02000115NGAS 000000000K00000100000002822000306272023142139N000000000000U11 007030400K 00000010020000002809
18	CUS0200011500100005143018 X X X X 0000000014264701 000N 0000000014264701	MTR02000115001000000000000000 A 000207246988000011500145115001450000020000NN NNG 000 0000000000	RDG02000115NGAS 000000000K00000100000000415000206272023140956N000400000010U11 004150400K 00000010140000000413
Cycle 02 Results 06.30.23 sds

 

[spectraflame]

Would the Transpose function be something to consider and have it select the 3 rows throughout the entire sheet?