Hello, | ||
I have a below data, I need a formula to merge multiple cells in 1 cell, as shown below in the output formula | ||
P/N | PO | OUTPUT |
A | 123 | 123 456 189 |
A | 456 | |
A | 189 | |
B | 89 | 89 698 |
B | 698 | |
C | 852 | 852 741 963 256 789 |
C | 741 | |
C | 963 | |
C | 256 | |
C | 789 | |
Hello, | ||
I have a below data, I need a formula to merge multiple cells in 1 cell, as shown below in the output formula | ||
P/N | PO | OUTPUT |
A | 123 | 123 456 189 |
A | 456 | |
A | 189 | |
B | 89 | 89 698 |
B | 698 | |
C | 852 | 852 741 963 256 789 |
C | 741 | |
C | 963 | |
C | 256 | |
C | 789 | |
[table="width: 500"]
[tr]
[td]Sub CombinePOs()
Dim Ar As Range
Range("B2", Cells(Rows.Count, "B").End(xlUp)).Copy Range("C2")
With Range("A2", Cells(Rows.Count, "A").End(xlUp))
.Copy Range("D2")
.Offset(, 3).Value = Evaluate("IF(" & .Address & "=" & .Offset(-1).Address & ",""""," & .Address & ")")
For Each Ar In .Offset(, 3).SpecialCells(xlBlanks).Areas
Ar(1).Offset(-1, -1) = Join(Application.Transpose(Ar(1).Offset(-1, -1).Resize(Ar.Count + 1)), vbLf)
Ar.Offset(, -1).Clear
Next
End With
Columns("D").Clear
End Sub[/td]
[/tr]
[/table]
Only if you have the version of Excel that has the TEXTJOIN function. If you do not have that function available in your version of Excel, you will need to use the macro I created for you. If you do have the right version of Excel, then unfortunately I cannot create a formula for you as I do not have the right version of Excel... you will have to wait for one of the other volunteers who does have it to respond.Thank you sir. But is there a way we can use formula instead of VBA.
Book1 | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | P/N | PO | Wrap Text | ||||
2 | A | 123 | 123 | 123 456 189 | |||
3 | A | 456 | 123,456 | ||||
4 | A | 189 | AAA | 123,456,189 | |||
5 | B | 89 | 89 | 89 698 | |||
6 | B | 698 | BB | 89,698 | |||
7 | C | 852 | 852 | 852 741 963 256 789 | |||
8 | C | 741 | 852,741 | ||||
9 | C | 963 | 852,741,963 | ||||
10 | C | 256 | 852,741,963,256 | ||||
11 | C | 789 | CCCCC | 852,741,963,256,789 | |||
Sheet347 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C2 | =IF(COUNTIF(A$2:A2,A2)=COUNTIF(A$2:A$11,A2),REPT(A2,COUNTIF(A$2:A$11,A2)),"") | |
D2 | =IF(A2=A1,D1&","&B2,B2) | |
E2 | =IF(COUNTIF(A$2:A2,A2)=1,SUBSTITUTE(VLOOKUP(REPT(A2,COUNTIF(A$2:A$11,A2)),C$2:D$11,2,0),",",CHAR(10)),"") |