Need help to combine cell values

[mu4you]

Hi Guru's,

Kindly help me on below..

Thanking you in advance.

 

[shrivallabha]

It is better to post workbook than posting a screenshot.

Having said that, please test below code on a backup which works for the data format you have posted. It will paste rearranged data on Sheet 2.

Sub ReArrange()
Dim lngLastRow As Long, lngRefRow As Long, lngCnt As Long
Dim DataArray() As Variant
lngLastRow = Cells.Find("*", [A1], xlValues, xlPart, xlByRows, xlPrevious, False).Row
ReDim DataArray(1 To Application.CountA(Range("A1:A" & lngLastRow).Value), 1 To 3)
For i = 1 To lngLastRow
    If Len(Range("A" & i).Value) > 0 Then
        If Len(Range("A" & i + 1).Value) > 0 Then
            lngRefRow = i
            lngCnt = lngCnt + 1
        Else
            lngRefRow = Range("A" & i).End(xlDown).Row - 1
            If lngRefRow > lngLastRow Then lngRefRow = lngLastRow
            lngCnt = lngCnt + 1
        End If
        DataArray(lngCnt, 1) = Range("A" & i).Value
        If i = lngRefRow Then
            DataArray(lngCnt, 2) = Range("B" & i).Value
            DataArray(lngCnt, 3) = Range("C" & i).Value
        Else
            DataArray(lngCnt, 2) = Replace(Application.Trim(Join(Application.Transpose(Range("B" & i & ":B" & lngRefRow).Value), " ")), " ", ",")
            DataArray(lngCnt, 3) = Replace(Application.Trim(Join(Application.Transpose(Range("C" & i & ":C" & lngRefRow).Value), " ")), " ", ",")
        End If
    End If
Next i
Sheets(2).UsedRange.Delete
Sheets(2).Range("A1").Resize(lngCnt, 3).Value = DataArray
End Sub

 

[mu4you]

Sir,
I copied your code and its working but I am not that much good in VB and I prefer in formulas.

I used some formulas and managed to get P/N's(column C) in cells as per my requirement but unable to get "supplier order no" column B.

your profile pic so cute... God bless him....

 

[bosco_yip]

Try this formula way with helper column,

1] Helper1, E2 copy down :

=IF(A2<>"",A2,E1)

2] Helper2, F2 copy down :

=TRIM(B2&IFERROR(" "&INDEX(F3:F$18,MATCH(E2,E3:E$18,0)),""))

3] Helper3, G2 copy down :

=C2&IFERROR(", "&INDEX(G3:G$18,MATCH(E2,E3:E$18,0)),"")

4] "Output" Cust. Ord No. A23, array formula (confirm enter with SHIFT+CTRL+ENTER) copy down :

=IFERROR(INDEX(A$2:A$17,SMALL(IF(A$2:A$17<>"",ROW(A$2:A$17)-ROW(A$2)+1,18),ROWS($1:1))),"")

5] "Output" Supp. Order No. B23, copy down :

=IF(A23="","",SUBSTITUTE(VLOOKUP(A23,A$2:G$17,6,0)," ",", "))

6] "Output" p/n #. C23, copy down :

=IF(A23="","",VLOOKUP(A23,A$2:G$17,7,0))

Regards
Bosco

 

[mu4you]

SIR ....

U ARE A LIFE SAVER .....

GREAT !!!!

NO WORDS SO SAY THANKS.

IS THERE ANYTHING I CAN DO FOR YOU.... PLEASE

 

[mu4you]

Dear Bosco,

Need one more help, If you can suggest me which Excel course I need to take either in Chandoo or anywhere else just to learn at least 10 % formulas in the way you use.

manzoor0076@gmail.com