Table Value Multiplay Formula

**Johny Smith** · 10-08-2008, 07:50 AM

I have a small problem here. I have worksheet with some data as on image 1

http://www.excelforum.com/attachment...1&d=1223465953

and I want a formula/function/code that can make data to look like on image 2

http://www.excelforum.com/attachment...1&d=1223465953

Any help will be appreciated

Thanks in advance

**darkyam** · 10-08-2008, 08:15 AM

Welcome to the forum. Please read the forum rules about posting a good thread title. If I assume that you want these people to appear in groups of 4, then you can use this in B1, dragged across and down: =OFFSET($A$1,MAX(0,ROUNDDOWN((ROW()-1)/4,0)*4)+COLUMN()-2,0)

**Johny Smith** · 10-08-2008, 09:51 AM

I wish to each person appear from every group in first column next to that group where that person belong. Number of persons in groups is different and it's for example from 2 to 10. In example on images are only two groups with 3 and 4 persons, but I have more than 1000 different groups.

**MickG** · 10-08-2008, 09:54 AM

Hi, I'm not sure if I'm suppose to reply , alhough someone else has.
Anyway !, It took some time to write so you might as well have it.
You Data starts "A2" on , Results in "a2" on
NB:-This code will overwritew your Data.

Dim Ray(), Last As Integer, Dn As Integer, Pst As Integer
Dim LstA As Integer, Hoz

Last = Range("A" & Rows.Count).End(xlUp).Row
ReDim Ray(1 To Last)

For Dn = 2 To Last
    Ray(Dn) = Range(Range("A" & Dn), Cells(Dn, Columns.Count).End(xlToLeft))
Next Dn
 
 Range(Range("A2"), Range("A" & Rows.Count).End(xlUp)).Resize(, 15).ClearContents

For Pst = 2 To Last
        LstA = Range("A" & Rows.Count).End(xlUp).Row + 1
        Range("A" & LstA & ":A" & LstA + UBound(Ray(Pst), 2) - 1).Value = Application.Transpose(Ray(Pst))
    For Hoz = LstA To LstA + UBound(Ray(Pst), 2) - 1
        Range(Cells(Hoz, "A"), Cells(Hoz, UBound(Ray(Pst), 2))).Offset(, 1).Value = Ray(Pst)
    Next Hoz
Next Pst

Regards Mick