Distribute several unique data from one cell to multiple cells

Volcom23 · Oct 9th 2015

What I'm trying to do is to distribute the date of encoding of several reports that I had merged in Excel into a set of columns where the dates are shown to each row of data. Here's a screenshot of the raw file:

[TABLE="width: 192"]

[tr]

[TD="width: 64"]Name[/TD]
[TD="width: 64"]Address[/TD]
[TD="width: 64"]Age[/TD]

[/tr]

[tr]

[td][/td]

[TD="class: xl63, align: right"]5-Jan-15[/TD]

[/tr]

[tr]

[td]

Child1

[/td]

[td]

Location1

[/td]

[TD="align: right"]10[/TD]

[/tr]

[tr]

[td]

Child2

[/td]

[td]

Location2

[/td]

[TD="align: right"]9[/TD]

[/tr]

[tr]

[td]

Child3

[/td]

[td]

Location3

[/td]

[TD="align: right"]8[/TD]

[/tr]

[tr]

[td][/td]

[TD="class: xl63, align: right"]8-Feb-15[/TD]

[/tr]

[tr]

[td]

Child4

[/td]

[td]

Location4

[/td]

[TD="align: right"]7[/TD]

[/tr]

[tr]

[td]

Child5

[/td]

[td]

Location5

[/td]

[TD="align: right"]6[/TD]

[/tr]

[/TABLE]

I'm trying to make it like this one:

[TABLE="width: 256"]

[tr]

[TD="width: 64"]Name[/TD]
[TD="width: 64"]Address[/TD]
[TD="width: 64"]Age[/TD]
[TD="width: 64"]Date encoded[/TD]

[/tr]

[tr]

[td]

Child1

[/td]

[td]

Location1

[/td]

[TD="align: right"]10[/TD]
[TD="class: xl65, align: right"]5-Jan-15[/TD]

[/tr]

[tr]

[td]

Child2

[/td]

[td]

Location2

[/td]

[TD="align: right"]9[/TD]
[TD="class: xl65, align: right"]5-Jan-15[/TD]

[/tr]

[tr]

[td]

Child3

[/td]

[td]

Location3

[/td]

[TD="align: right"]8[/TD]
[TD="class: xl65, align: right"]5-Jan-15[/TD]

[/tr]

[tr]

[td]

Child4

[/td]

[td]

Location4

[/td]

[TD="align: right"]7[/TD]
[TD="class: xl65, align: right"]8-Feb-15[/TD]

[/tr]

[tr]

[td]

Child5

[/td]

[td]

Location5

[/td]

[TD="align: right"]6[/TD]
[TD="class: xl65, align: right"]8-Feb-15[/TD]

[/tr]

[/TABLE]

Any idea?

**royUK** · Oct 9th 2015

Re: Distribute several unique data from one cell to multiple cells

An example workbook would help

Volcom23 · Oct 9th 2015

Re: Distribute several unique data from one cell to multiple cells

here's the excel workbook

https://drive.google.com/file/…PSjc0ODA/view?usp=sharing

Thank you!

Volcom23 · Oct 12th 2015

Re: Distribute several unique data from one cell to multiple cells

This forum doesn't help anything. Very much disappointed.

jindon · Oct 12th 2015

Re: Distribute several unique data from one cell to multiple cells

For the data provided in the link.

Code

Sub test()
    Dim r As Range
    With Range("a3", Range("a" & Rows.Count).End(xlUp))
        For Each r In .SpecialCells(2).Areas
            r(0, 3).Copy r.Offset(, 3)
        Next
        .SpecialCells(4).EntireRow.Delete
    End With
End Sub

Distribute several unique data from one cell to multiple cells

Participate now!

Share