How to consolidate multi-row data into a single row?

[rockfalls3]

I have a worksheet with over 2000 rows and 6 columns and I would like
to
consolidate some multi-row entries into a single line.

The format for many (but not all) of the entries is this:

text1 | text2 | text3 | titleA | titleB | titleC
| | | 123456 | somestuff |
calculatedvalueX

Where the first three cells of the second line are blank.

I would like to perform the following action:
1) Cut the values in the last three cells of the second line.
2) Paste those values into the equivalent cells of the first line.
3) Delete the second line (now that it does not have any data).

Unfortunately, some entries have this two-line format, while other
entries may have the first line with all six columns populated but
then "n" number of rows with only cell columns D, E, and F populated
(and those first three columns each having merged cells of n-rows).
The entries with "n" number of rows are randomly distributed in the
2000+ row spreadsheet, so I can't know as I'm scanning through the
spreadsheet when I'll encounter a two-line or an n-line entry. For
that
reason, I thought a macro I could call while manually scrolling
through
the rows would work best (later, I'll figure out how to deal with the
n-line
entries, perhaps by putting their values in successive columns in the
same row).

So, how can such a macro be constructed and run so that it does not
have absolute references to cells and rows but can work equally as
well
at row #4 as at row # 444?

(If there's VB code that can do this automagically without the manual
macro application, all the better.)

advTHANKSance.

 

[sbitaxi]

I have a worksheet with over 2000 rows and 6 columns and I would like
to
consolidate some multi-row entries into a single line.

The format for many (but not all) of the entries is this:

text1  |  text2  |  text3  |  titleA    |  titleB        |  titleC
         |           |            |  123456 | somestuff  |
calculatedvalueX

Where the first three cells of the second line are blank.

I would like to perform the following action:
    1) Cut the values in the last three cells of the second line.
    2) Paste those values into the equivalent cells of the first line..
    3) Delete the second line (now that it does not have any data).

Unfortunately, some entries have this two-line format, while other
entries may have the first line with all six columns populated but
then "n" number of rows with only cell columns D, E, and F populated
(and those first three columns each having merged cells of n-rows).
The entries with "n" number of rows are randomly distributed in the
2000+ row spreadsheet, so I can't know as I'm scanning through the
spreadsheet when I'll encounter a two-line or an n-line entry. For
that
reason, I thought a macro I could call while manually scrolling
through
the rows would work best (later, I'll figure out how to deal with the
n-line
entries, perhaps by putting their values in successive columns in the
same row).

So, how can such a macro be constructed and run so that it does not
have absolute references to cells and rows but can work equally as
well
at row #4 as at row # 444?

(If there's VB code that can do this automagically without the manual
macro application, all the better.)

advTHANKSance.

This will move data in columns D, E, F into A, B, C respectively for
the previous row, if the cell in column A is blank.

A B C D E F
1 2 3 4 5 6
7 8 9

Will become

A B C D E F
7 8 9 4 5 6

Is that what you were after?

Regards,

Steven


Sub CombineRows()
Dim WS As Worksheet
Dim Rng1 As Range
Dim MyCell As Range

Set WS = ActiveSheet
Set Rng1 = WS.Range("A1:A2000")

For Each MyCell In Rng1
If MyCell.Value = "" Then
MyCell.Offset(-1, 0).Value = MyCell.Offset(0, 3).Value
MyCell.Offset(-1, 1).Value = MyCell.Offset(0, 4).Value
MyCell.Offset(-1, 2).Value = MyCell.Offset(0, 5).Value
Rows((MyCell.Row) & ":" & (MyCell.Row)).Delete
End If
Next
End Sub

 

[rockfalls3]

This will move data in columns D, E, F into A, B, C respectively for
the previous row, if the cell in column A is blank.

A  B  C  D  E  F
1  2  3  4  5  6
         7  8  9

Will become

A  B  C  D  E  F
7  8  9  4  5  6

Is that what you were after?

Regards,

Steven

Steven, Thanks for the quick reply. You're close, but not exact.
I was looking for (spacing may be off because I'm not using Courier
font):

Header: A B C D E F
Line 1: 1 2 3 4 5 6
Line 2: 7 8 9

to become

Header: A B C D E F
Line 1: 1 2 3 7 8 9

where "A B C D E F" is the header of the whole spreadsheet,
but "4 5 6" was the header information for sub-attributes of the
record/entry named "1 2 3". If the entry was:

Header: A B C D E F
Line 1: 1 2 3 4 5 6
Line 2: 7 8 9
Line 3: 5 7 4

I would want the result to be something like:

Header: A B C D E F
Line 1: 1 2 3 7 8 9 5 7 4

so that all the data for "1 2 3" now appears on one line and the
data/cell values of those second and third lines of the entry
appear on Line 1, and then the (now blank) Lines 2 and 3 are
deleted.

Thanks again.

 

[sbitaxi]

Steven, Thanks for the quick reply. You're close, but not exact.
I was looking for (spacing may be off because I'm not using Courier
font):

Header:  A  B  C  D  E  F
Line 1:    1  2   3  4  5  6
Line 2:                7  8  9

to become

Header:  A  B  C  D  E  F
Line 1:    1  2   3  7  8  9

where "A B C D E F" is the header of the whole spreadsheet,
but "4 5 6" was the header information for sub-attributes of the
record/entry named "1 2 3". If the entry was:

Header:  A  B  C  D  E  F
Line 1:    1  2   3  4  5  6
Line 2:                7  8  9
Line 3:                5  7  4

I  would want the result to be something like:

Header:  A  B  C  D  E  F
Line 1:    1  2   3  7   8  9  5  7 4

so that all the data for "1 2 3" now appears on one line and the
data/cell values of those second and third lines of the entry
appear on Line 1, and then the (now blank) Lines 2 and 3 are
deleted.

Thanks again.

Hello:

A few questions -
- Are there a maximum number of lines that may be blank?
- Are the first 3 cells always blank in those instances or may it also
be the first two or just one? e.g.
A B C D E F
1 2 3 4 5 6
7 8 9 (3 blank cells)
5 7 4 (3 blank cells)
9 8 7 6 5 (1 blank cell)
4 3 2 1 (2 blank cells)

- will column D always have data?

So you currently want data replaced in columns D, E and F, and any
other data inserted into the columns that follow.

I'll have something over the weekend. I've got a sketch and it
shouldn't take me long after that.


Steven

 

[rockfalls3]

Hello:

A few questions -
- Are there a maximum number of lines that may be blank?
- Are the first 3 cells always blank in those instances or may it also
be the first two or just one? e.g.
A  B  C  D  E  F
1  2  3  4  5  6
         7  8  9 (3 blank cells)
         5  7  4 (3 blank cells)
   9  8  7  6  5 (1 blank cell)
      4  3  2  1 (2 blank cells)

- will column D always have data?

So you currently want data replaced in columns D, E and F, and any
other data inserted into the columns that follow.

I'll have something over the weekend. I've got a sketch and it
shouldn't take me long after that.

Steven- Hide quoted text -

- Show quoted text -

In answer to your questions:
1) The number of blank lines will be determined by the number of
"data" lines
for each "entry" line. For instance, perusing the file I just saw
an entry with
18 separate "data" lines. That would mean 54 columns of data
populated
in the "entry" line, after the first three populated columns for
that entry, after
which the now-blank 18 lines would be deleted. [If it is easier, I
suppose the
blank lines could be found manually using a filter and then
deleted.]
2) The first three cells of each new entry's row are always populated.
3) Column D may be blank. There are 9 entries with columns D, E, and F
blank
in the "entry" line, and no associated "data" line. I.e., their
information (such
as it is...or isn't) is already on a single line.

Thank you again for all your help.
Please accept this e-beer:
c[[]]

 

[sbitaxi]

Hello:

I think I have a solution for you, try the following code on a sample
of your data. Please remember, there is no undo history for Macros so
test it first before using it on your final data.

Let me know if you have any questions. To delete the records, simply
remove ".Select" at the end. Watch for unintentional line breaks when
copying code.


Steven


Sub CombineRows()
Dim WS As Worksheet ' Worksheet containing data
Dim Rng1 As Range ' Record range
Dim MyCell As Range ' Range Variable
Dim R As Long ' Count of blank rows
Dim COffset As Long ' Incrementing Column offset for
merging rows
Dim ROffset As Long ' Incrementing Row offset for merging
rows
Dim LastR As Long ' Last row of data

'Disables screen updating and calculations to speed up macro
With Application
.DisplayAlerts = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
.StatusBar = False
End With

Set WS = ActiveSheet

' Establishes last row of data, using column D
LastR = WS.Cells(Rows.Count, "D").End(xlUp).Row

'**** IF THE FIRST ROW CONTAINS HEADERS, USE THE FOLLOWING LINE AND
REM THE NEXT
' Set Rng1 = WS.Range("A2:A" & LastR)

' Establishes range containing records REM IF HEADER ROW EXISTS
Set Rng1 = WS.Range("A1:A" & LastR)

For Each MyCell In Rng1
R = 0

' If there is any data in column A of row
If MyCell.Value > "" Then
' Counts blank rows between records
Do Until MyCell.Offset(R + 1, 0).Value > ""
' Exits before it loops beyond last row of data
If MyCell.Offset(R, 0).Address = Cells(LastR + 1, "A").Address
Then Exit Do
R = R + 1
Loop

' Combines row data
For ROffset = 0 To R
For COffset = 0 To 3
MyCell.Offset(0, (ROffset * 3) + COffset + 3).Value = _
MyCell.Offset(ROffset + 1, COffset + 3).Value
Next COffset
Next ROffset
End If
Next MyCell

'Filters and deletes blank rows
Rng1.AutoFilter Field:=1, Criteria1:="="
Rng1.Offset(1, 0).EntireRow.SpecialCells(xlCellTypeVisible).Select
'Delete 'Remove select to delete rows

' Restores screen updating and calculations
With Application
.DisplayAlerts = True
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.StatusBar = True
End With
End Sub