Concatenate (combine) Header Row with Variabl

Microsoft Excel 2003 (full)
June 29, 2009 at 18:18:34
Specs: Windows XP
Concatenate (combine) Header Row with Variable Number of Detail Rows

I need help writing an Excel VBA routine.

My input worksheet consists of rows, with 1 cell in each row, in the following format:

Header Row: AA . . .DATA . . .* - * will a number from 1 (not 01) to 99. i. e. 1,2,3 , , , ,97,98,99. AA starts in position 2 and * starts in position 20 of the cell.

Detail Row: If * in Header Row is 1, detail row will have a 1 in position 2 and there will one detail row.

If * in Header Row is 2, there will be 2 detail rows. The first detail row will have a 1 in position 2 and the second detail row will have a 2 in position 2.

If * in Header Row is 3, there will be 3 detail rows. The first detail row will have a 1 in position 2, the second detail row will have a 2 in position 2, and the third detail row will have a 3 in position 2.

And so on to a maximum of 99.

Example:

AA . . .DATA . . .1
1 Data
AA . . .DATA . . .2
1 Data
2 Data
AA . . .DATA . . .3
1 Data
2 Data
3 Data

I want my output in another worksheet to contain rows with 1 cell with the header row as a prefix to each of the details row(s). Insert a slash (/) between the header and the detail row.

Example”

AA . . .DATA . . .1/ 1 Data
AA . . .DATA . . .2/ 1 Data
AA . . .DATA . . .2/ 2 Data
AA . . .DATA . . .3/ 1 Data
AA . . .DATA . . .3/ 2 Data
AA . . .DATA . . .3/ 3 Data


See More: Concatenate (combine) Header Row with Variabl

Report •


#1
June 30, 2009 at 07:34:03
First, thanks for the detailed explanation of how your sheet is laid out. Knowing what you are starting with and what you want to end up with really helps.

However, there are 2 more pieces of information we need:

1 - Are the values for the number of Data rows given in the Header row always at the end of the Header row as in your examples, or are there more characters after that value? If there are more characters, are there always more characters or will some Header rows end in that value?

2 - Do your Headers rows really start with " AA"? The reason I ask is that we have to find something that will help the VBA code determine what is a Header row and what is a Data row. If they really start with " AA", then it's easy. If not, things get a little more interesting. Maybe Header rows all start with text, while Data rows start with a number, or something like that?


Report •

#2
June 30, 2009 at 09:14:55
Responses:

(1) Yes. The last 1 (1-9) or 2 (10-99) characters in the row.

(2) Header rows will always start with the same 2 characters. Again, these 2 characters are preceded by a space. I used AA in the example for simplicity. I can handle changing these to what I need. Ha Ha!

Thank you.


Report •

#3
June 30, 2009 at 09:27:20
Change the " AA" as required...

Sub HeaderData()
'Determine length of list
 lastRow = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Loop through rows
  For Each cell In Range("A1:A" & lastRow)
'Is cell a Header Row?
   If Left(cell, 3) = " AA" Then
'If yes, then extract Data row value
    numData = Mid(cell, 20, 2)
'Loop through Data rows, combining Header with each Data row
     For myRow = 1 To numData
        newRow = newRow + 1
        Sheets(2).Range("A" & newRow) = cell & "/" & cell.Offset(myRow, 0)
      Next
    End If
   Next
End Sub


Report •

Related Solutions

#4
June 30, 2009 at 12:13:17
Thank for the quick response. Has an issue. I believe my specs were accurate.

Use this as input:

AA 64 27XYZ MNO 1 245X 319 123R LIST Y1
1 aaaaaa AJ2 E ABC F 0 ETI 123S
AA 87 27XYZ XQR 1 635A 319 123R LIST Y1
1 bbbbbb W ABC F 0 ETI 123S
AA 102 27XYZ 789 C8 615A 319 DADS LIST Y1
1 cccccc Q ABC F 0 ETI DADS ABNR
AA 102 27XYZ 789 C8 615A 319 123R LIST Y1
1 dddddd U ABC F 0 OB ETI 123R
AA 109 27XYZ STU 1 825A 319 123R LIST Y1
1 eeeeee Q ABC F 0 OB ETI 123R
AA 125 27XYZ DEF E32 415X 318 123R LIST Y5
1 ffffffAI2 E ABC F 0 OB ETI 123R OTHS
2 gggggg Q ABC 14C F 0 FF ETI 123R
3 hhhhhh M ABC F 0 ETI 123C 123R
4 iiiiii Q ABC F 0 OB ETI 123S
5 jjjjjj M ABC F 0 FF ETI 123C 123R
AA 127 27XYZ DEF E32 640A 319 123R LIST Y10
1 kkkkkkD AS6 Q ABC F 0 OB ETI 123S
2 kkkkkkD AS6 Q ABC F 0 OB ETI 123S
3 kkkkkkD AS6 Q ABC F 0 OB ETI 123S
4 kkkkkkD AS6 Q ABC F 0 OB ETI 123S
5 kkkkkkD AS6 Q ABC F 0 OB ETI 123S
6 kkkkkkD AS6 Q ABC F 0 OB ETI 123S
7 kkkkkkD AS6 Q ABC F 0 OB ETI 123S
8 kkkkkkD AS6 Q ABC F 0 OB ETI 123S
9 kkkkkkD AS6 Q ABC F 0 OB ETI 123S
10 kkkkkkD AS6 Q ABC F 0 OB ETI 123S
AA 135 27XYZ DEF E32 150X 319 123R LIST Y1
1 llllll Q ABC F 0 FF ETI 123S
AA 139 27XYZ DEF E32 710X 319 123R LIST Y2
1 mmmmm AJ4 Q ABC F 0 ETI 123R
2 nnnnnn W ABC F 0 OB ETI 123C
AA 162 27XYZ HIJ C13 815X 318 DADS LIST Y1
1 oooooo G ABC 6D F 0 FF ETI DADS
AA 164 27XYZ HIJ C13 625A 318 DADS LIST Y2
1 XXXXXX G ABC 2C F 0 OB ETI 123C DADS
2 qqqqqq G ABC F 0 OB ETI DADS

Thank you.


Report •

#5
June 30, 2009 at 12:26:37
re: I believe my specs were accurate

From your OP:

Header Row: AA . . .DATA . . .* - * will a number from 1 (not 01) to 99. i. e. 1,2,3 , , , ,97,98,99.

AA starts in position 2 and * starts in position 20 of the cell.

Looking at your latest examples, I don't see where the * (the number of Data rows) starts in position 20.

Please clarify before I offer another solution.


Report •

#6
June 30, 2009 at 12:38:23
My apologies. Forgive me. I'm a typical "user".

AA 64 27XYZ MNO 1 245X 319 123R LIST Y1

AA 125 27XYZ DEF E32 415X 318 123R LIST Y5

The number of detail rows is in a variable position. It follows the "List Y" at the end of each header row and is the last character(s) in the row. See the 1 and 5 above.

I'm so sorry.


Report •

#7
June 30, 2009 at 12:56:10
Try this:

Replace:

'If yes, then extract Data row value
    numData = Mid(cell, 20, 2)

With:

'If yes, then extract Data row value
    numData = Right(cell, 2)
     If Not IsNumeric(Left(numData, 1)) _
        Then numData = Right(numData, 1)


Report •

#8
June 30, 2009 at 13:51:34
PERFECT! You are awesome! Thank you so very much.

Report •

#9
June 30, 2009 at 14:44:08
Glad I could help.

Report •

#10
July 6, 2009 at 20:04:29
I’ve been hit with another change to the input data. Cells following a data row (numeric position 1) may be followed by data rows (alpha position 1 but not AA) that must be combined with the previous data row (numeric position 1). These rows (alpha position 1) must be appended to the previous data row with a slash (/) between each appended row.

Please refer to the following example.

Input:

AA 64 27XYZ MNO 1 245X 319 123R LIST Y1
1 aaaaaa AJ2 E ABC F 0 ETI 123S
XYZ
ABC
AA 87 27XYZ XQR 1 635A 319 123R LIST Y1
1 bbbbbb W ABC F 0 ETI 123S
AA 102 27XYZ 789 C8 615A 319 DADS LIST Y1
1 cccccc Q ABC F 0 ETI DADS ABNR
ABC
AA 102 27XYZ 789 C8 615A 319 123R LIST Y1
1 dddddd U ABC F 0 OB ETI 123R
AA 109 27XYZ STU 1 825A 319 123R LIST Y1
1 eeeeee Q ABC F 0 OB ETI 123R
AA 125 27XYZ DEF E32 415X 318 123R LIST Y5
1 ffffffAI2 E ABC F 0 OB ETI 123R OTHS
2 gggggg Q ABC 14C F 0 FF ETI 123R
3 hhhhhh M ABC F 0 ETI 123C 123R
ABC
DEF
4 iiiiii Q ABC F 0 OB ETI 123S
5 jjjjjj M ABC F 0 FF ETI 123C 123R
AA 127 27XYZ DEF E32 640A 319 123R LIST Y10
1 kkkkkkD AS6 Q ABC F 0 OB ETI 123S
2 kkkkkkD AS6 Q ABC F 0 OB ETI 123S
3 kkkkkkD AS6 Q ABC F 0 OB ETI 123S
ABC
DEF
GHI
JKL
4 kkkkkkD AS6 Q ABC F 0 OB ETI 123S
5 kkkkkkD AS6 Q ABC F 0 OB ETI 123S
6 kkkkkkD AS6 Q ABC F 0 OB ETI 123S
ABC
7 kkkkkkD AS6 Q ABC F 0 OB ETI 123S
8 kkkkkkD AS6 Q ABC F 0 OB ETI 123S
ABC
DEF
9 kkkkkkD AS6 Q ABC F 0 OB ETI 123S
10 kkkkkkD AS6 Q ABC F 0 OB ETI 123S
AA 135 27XYZ DEF E32 150X 319 123R LIST Y1
1 llllll Q ABC F 0 FF ETI 123S
AA 139 27XYZ DEF E32 710X 319 123R LIST Y2
1 mmmmm AJ4 Q ABC F 0 ETI 123R
ABC
DEF
2 nnnnnn W ABC F 0 OB ETI 123C
AA 162 27XYZ HIJ C13 815X 318 DADS LIST Y1
1 oooooo G ABC 6D F 0 FF ETI DADS
AA 164 27XYZ HIJ C13 625A 318 DADS LIST Y2
1 XXXXXX G ABC 2C F 0 OB ETI 123C DADS
2 qqqqqq G ABC F 0 OB ETI DADS

Output:

AA 64 27XYZ MNO 1 245X 319 123R LIST Y1/1 aaaaaa AJ2 E ABC F 0 ETI 123S/XYZ/ABC
AA 87 27XYZ XQR 1 635A 319 123R LIST Y1/1 bbbbbb W ABC F 0 ETI 123S

Is this doable?


Report •

#11
July 6, 2009 at 21:08:55
Well, it might be doable, but not by a simple modification of the code already offered.

Originally, your requirements stated that the number of rows to concatenated could be determined by the value at the end of the Header row. That allowed a loop to be set up based on that value.

Now, if I understand you correctly, we have no idea how many rows will be have to be concatenated other than it will be at least as many as the value at the end of the Header row. In addition, some rows will be concatenated with the header row, and some rows will be concatenated with a row that has already been concatenated with a header row.

Since I essentially have to start from scratch, I certainly hope this is the last change in the requirements.


Report •

#12
July 7, 2009 at 07:30:05
Try this:

Sub HeaderData()
'Determine length of Sheet1 list
 lastRow = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Loop through rows
  For Each cell In Sheets(1).Range("A1:A" & lastRow)
'Is cell a Header Row?
   If Left(cell, 3) = " AA" Then
'Store header
    myHeader = cell
'Initialize Sheet 1 row counter
    nxt_Sht1_row = 1
'Loop through cells until the next Header is found
     Do Until Left(cell.Offset(nxt_Sht1_row, 0), 3) = " AA"
'If cell is empty, we're done
      If cell.Offset(nxt_Sht1_row, 0) = "" Then Exit Sub
'If position 2 is a number, then increment Sheet 2 row counter,
'concatenate Header and Numeric Data, and increment Sheet 1 row counter
       If IsNumeric(Mid(cell.Offset(nxt_Sht1_row, 0), 2, 1)) Then
        nxt_Sht2_row = nxt_Sht2_row + 1
         Sheets(2).Range("A" & nxt_Sht2_row) = myHeader & "/" & cell.Offset(nxt_Sht1_row, 0)
          nxt_Sht1_row = nxt_Sht1_row + 1
'If Position 2 is a letter, concatenate Data onto previous concatenation
'and Increment Sheet 1 row counter
       Else:
        Sheets(2).Range("A" & nxt_Sht2_row) _
              = Sheets(2).Range("A" & nxt_Sht2_row) & "/" & cell.Offset(nxt_Sht1_row, 0)
        nxt_Sht1_row = nxt_Sht1_row + 1
       End If
'Loop until next Header is found
    Loop
   End If
  Next
End Sub


Report •

#13
July 7, 2009 at 07:48:32
I do too! Thank you.

Report •


Ask Question