Excel Moving rows - version 2 1

[JeanW]

You guys already saved me, and now I have another problem.
Knowing nearly nothing further than basic (and even lower) I hope somebody can help me with this.

I think it's a Word doc that is copy/paste into an Excel spreadsheet, and I don't have the Word doc.

The result in Excel is:

A1 - B1 - C1 - D1 = column header - no problem
A2 = value (Abide) - B2 = value (Abode) - C2 = value (Abode)D2 = value (To continue - To reside)

but....

The problem is in column D4.
I think in the Word doc, there were 2 (sometimes more) 'definitions'.

This results in Excel with cell A2,B2,C2 has a single value, but the 'multivalue' for D2 (To continue and To reside) are set in two cells.
To continue is in cell D2 and To reside is in cell D3, where cell A2, B2, C2 are in hight the same as cell D2 and D3 together.

Hope I made it clear.....

The values has to be imported in a databese (FileMaker).

Therefore I need to tweak the Excel file first.

Basically I need all the values in one row.

I tried it maually, but with 7000+ rows.....

Any idea, not to technical ?

TIA

 

[DirkStruan]

Please confirm if I understand correctly:
Y
ou have entries in A2 to C2, then any number of entries relating to A2-C2 in the D column (in this case 2 entries in D2 & D3) and then you have another block of data underneath? So it looks like the following

A B C D
x x x x
x
x x x x
x
x
x
x x x x

and you want to transform it into 1 row per entry (with a variable number of columns)?

 

[JeanW]

>>Please confirm if I understand correctly:
>>You have entries in A2 to C2,
Yes

>> then any number of entries relating to A2-C2 in the D column (in this case 2 entries in D2 & D3)
Yes

>> and then you have another block of data underneath?
Not always, that's the problem...

>>So it looks like the following

A B C D
1 x x x x
2 x
3 x x x x
4 x
5 x
6 x
7 x x x x

Yes

>>and you want to transform it into 1 row per entry (with a variable number of columns)?
Yes, something like:

A B C D E F G
1 x x x x x(from row 2)
2 x x x x x(from row 4) x(from row 5) x(from row 6)
3 x x x x

Thanks for help

 

[DirkStruan]

Ok couple of spreadsheet functions and some VBA (as we're using a macro you may want to try this on a copy of your sheet):

Set up the following on the spreadsheet

E2=1
E3=IF(A3="",E2,E2+1)
F2=COUNTIF(E:E,"="&E2)

Then copy thr functions in E3 & F2 down as far as the D column is filled with data

The run the following macro

Sub ToLInes()
Count = Range("E65536").End(xlUp).Value
r = Range("E65536").End(xlUp).Row
lastrow = r
maxN = 0

For x = Count To 1 Step -1
    n = Range("E" & r).Offset(0, 1).Value - 1
    
    If n > maxN Then maxN = n
    
    Range("D" & r & ":D" & r - n).Copy
    Range("E" & r - n).PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
        , transpose:=True
    r = r - n - 1
Next x

Range("A2:" & Range("D2").Offset(lastrow - 2, maxN + 1).Address).AutoFilter Field:=1, Criteria1:="="

z = 1
While Cells(z, 1).Value <> ""
    z = z + 1
Wend
Rows(z & ":" & lastrow).EntireRow.Delete

Selection.AutoFilter

End Sub

Hopefully that should do it.

D

 

[JeanW]

Right on, it does.

Thank you.