Tranpose data Problem

Hi, I need help on transposing the data in the desired format.

I have attached the sample file for your help. There is a set of data for a defined period for certain geography.

In the attached file, there is a CI visit tab which have data on the site visited by CI in a geography. Cell J3 has site visited on a particular day , each site seperated with comma.

What I require is the transpose of these sites along with the Zone, CI name, Cluster and date on which site was visited. sample view of required data is given in the tab "result".

Looking forward to your response on this. Thanks in advance.

Regards

Is there a limit of how many numbers per box seperated by commas?

No, there is no limit to numbers per box. Could be 10 or 15 or 24 etc... no limit to this. If you wanna fix it then take 35 as max per box..

Hi

try this macro - it copies the data from row 15 on sheet "Result"

Sub transpose_data()

Dim ColNum As Long, RowNum As Long, LastRow As Long, DestRow As Long, SiteArr, SiteNum As Long
LastRow = Sheets("CI Visit").UsedRange.SpecialCells(xlCellTypeLastCell).Row
DestRow = 15
For RowNum = 3 To LastRow
    For ColNum = 5 To 11
        If Sheets("CI Visit").Rows(RowNum).Columns(ColNum).Value <> "" Then
            SiteArr = Split(Sheets("CI Visit").Rows(RowNum).Columns(ColNum).Value, ",")
            For SiteNum = LBound(SiteArr) To UBound(SiteArr)
                Sheets("Result").Rows(DestRow).Columns(1).Value = SiteArr(SiteNum)
                Sheets("Result").Rows(DestRow).Columns(2).Value = Sheets("CI Visit").Rows(RowNum).Columns(1).MergeArea.Cells(1, 1).Value
                Sheets("Result").Rows(DestRow).Columns(3).Value = Sheets("CI Visit").Rows(RowNum).Columns(3).Value
                Sheets("Result").Rows(DestRow).Columns(4).Value = Sheets("CI Visit").Rows(RowNum).Columns(2).Value
                Sheets("Result").Rows(DestRow).Columns(5).Value = Sheets("CI Visit").Rows(2).Columns(ColNum).Value
                DestRow = 1 + DestRow
            Next SiteNum
        End If
    Next ColNum
    
Next RowNum
End Sub

Thanks for the reply. It worked for me!! Thanks a ton...