Excel Formula - Combine order numbers

hrayani

Well-known Member
Hello Friends,

I am using the below formula (Column C) which I also got it form this useful site..

Is there any possibility to have the results as stated in column D

Book1
ABCD
1REF #PO #Concatenated ValuesRequired
21123456123456123456
31123457123456 - 123457123456 - 7
41123490123456 - 123457 - 123490123456 - 7 - 90
52234909234909234909
62234876234909 - 234876234909 - 876
72123456234909 - 234876 - 123456234909 - 876 - 123456
83345678 A345678 A345678 A
93345678 B345678 A - 345678 B345678 A - B
103345678 C345678 A - 345678 B - 345678 C345678 A - B - C
114123456123456123456
124388618123456 - 388618123456 - 388618
134123457123456 - 388618 - 123457123456 - 388618 - 123457
Sheet1
Cell Formulas
RangeFormula
C2:C13C2=IF(A2<>A1,B2,IF(ISNUMBER(SEARCH(B2,C1)),C1,C1&" - "&B2))

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
It's not quite the formula solution you asked for, but I was able to create a User Defined Function to do what you want.

Book5
ABCD
1REF #PO #Concatenated ValuesRequired
21123456123456123456
31123457123456 - 7123456 - 7
41123490123456 - 7 - 90123456 - 7 - 90
52234909234909234909
62234876234909 - 876234909 - 876
72123456234909 - 876 - 123456234909 - 876 - 123456
83345678 A345678 A345678 A
93345678 B345678 A - B345678 A - B
103345678 C345678 A - B - C345678 A - B - C
114123456123456123456
124388618123456 - 388618123456 - 388618
134123457123456 - 388618 - 123457123456 - 388618 - 123457
Sheet1
Cell Formulas
RangeFormula
C2:C13C2=PoNums(B2)

VBA Code:
``````Public Function PoNums(ByRef CurPoNo As Range) As String
Dim PreRefNo As String, CurRefNo As String
Dim PrePoNo As String
Dim PreConCatPoNo As String

PreRefNo = CurPoNo.Offset(-1, -1)
CurRefNo = CurPoNo.Offset(0, -1).Value
PrePoNo = CurPoNo.Offset(-1, 0).Value
PreConCatPoNo = CurPoNo.Offset(-1, 1).Value

If PreRefNo <> CurRefNo Then
PoNums = CurPoNo
Exit Function
End If

If Len(PrePoNo) = Len(CurPoNo) Then
For i = 1 To Len(PrePoNo)
If Mid(PrePoNo, i, 1) <> Mid(CurPoNo, i, 1) Then
PoNums = PreConCatPoNo & " - " & Mid(CurPoNo, i, Len(CurPoNo))
Exit Function
End If
Next i
Else
PoNums = PreConCatPoNo & " - " & CurPoNo
Exit Function
End If
End Function``````

It's not quite the formula solution you asked for, but I was able to create a User Defined Function to do what you want.
Thanks for this.... but it is not giving results when changes are been made in Column A (REF #) unless & until the main cell is been edited
example if cell A4 is changed to 0 then I need to edit the cell C4 & press enter for the changes to take effect in cell C4

Check that your calculation method isn't set to manual

Check that your calculation method isn't set to manual
I already checked it --- its set to automatic
other calculations in the sheet works fine

If any cell is changed in column B then its changing in real time
But this is not happening with a change in column A

also the original data will have different columns - just in case the udf needs amendment

I amended the function so that you will have to select the cells containing the relevant data. You can see the parameter names by typing into a cell `=PoNums(` and then pressing Ctl-A which will show the Function Arguments pop up. I believe this also solves the other issue of the function not updating when column A is changed.

VBA Code:
``````Public Function PoNums(CurRefNo As Range, PreRefNo As Range, CurPoNo As Range, PrePoNo As Range, PreConCatPoNo As Range) As String

If PreRefNo <> CurRefNo Then
PoNums = CurPoNo
Exit Function
End If

If Len(PrePoNo) = Len(CurPoNo) Then
For i = 1 To Len(PrePoNo)
If Mid(PrePoNo, i, 1) <> Mid(CurPoNo, i, 1) Then
PoNums = PreConCatPoNo & " - " & Mid(CurPoNo, i, Len(CurPoNo))
Exit Function
End If
Next i
Else
PoNums = PreConCatPoNo & " - " & CurPoNo
Exit Function
End If
End Function``````

hrayani 20210808.xlsm
ABCD
1REF #PO #Concatenated ValuesRequired
21123456123456123456
31123457123456 - 7123456 - 7
41123490123456 - 7 - 90123456 - 7 - 90
52234909234909234909
62234876234909 - 876234909 - 876
72123456234909 - 876 - 123456234909 - 876 - 123456
83345678 A345678 A345678 A
93345678 B345678 A - B345678 A - B
1033456789 C345678 A - B - 3456789 C345678 A - B - C
114123456123456123456
124388618123456 - 388618123456 - 388618
134123457123456 - 388618 - 123457123456 - 388618 - 123457
Sheet1
Cell Formulas
RangeFormula
C2:C13C2=PoNums(A2,A1,B2,B1,C1)

Many thanks ... that solves the problem

I was trying to create a formula & came up with this... very messy though

VBA Working.xlsm
ABC
1REF #PO #Concatenated Values
21123456123456
31123457123456 - 7
41123490123456 - 7 - 90
52234909234909
62234876234909 - 876
72123456234909 - 876 - 123456
83345678 A345678 A
93345678 B345678 A - B
103345678 C345678 A - B - C
114123456123456
124388618123456 - 388618
134123457123456 - 388618 - 123457
hr_working
Cell Formulas
RangeFormula
C2:C13C2=IF(A2<>A1,B2,IF(ISNUMBER(SEARCH(B2,C1)),C1,C1&" - "&MID(B2,IF(MID(B1,1,10)=MID(B2,1,10),10,IF(MID(B1,1,9)=MID(B2,1,9),9,IF(MID(B1,1,8)=MID(B2,1,8),8,IF(MID(B1,1,7)=MID(B2,1,7),7,IF(MID(B1,1,6)=MID(B2,1,6),6,IF(MID(B1,1,5)=MID(B2,1,5),5,IF(MID(B1,1,4)=MID(B2,1,4),4,IF(MID(B1,1,3)=MID(B2,1,3),3,IF(MID(B1,1,2)=MID(B2,1,2),2,IF(MID(B1,1,1)=MID(B2,1,1),1,0))))))))))+1,99)))

Replies
9
Views
315
Replies
3
Views
93
Replies
12
Views
378
Replies
4
Views
419
Replies
14
Views
471

1,196,506
Messages
6,015,593
Members
441,904
Latest member
edris Alsatouf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back