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

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?

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.

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

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.

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.

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.