Excel Formula - Combine order numbers

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,280
Office Version
  1. 2016
Platform
  1. Windows
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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Crystalyzer

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows
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
 

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,280
Office Version
  1. 2016
Platform
  1. Windows
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
 

Crystalyzer

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows
Check that your calculation method isn't set to manual
 

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,280
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
 

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,280
Office Version
  1. 2016
Platform
  1. Windows
also the original data will have different columns - just in case the udf needs amendment
 

Crystalyzer

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

things to mention when ASKING your question. SMH

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.

1628514692050.png


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)
 
Solution

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,280
Office Version
  1. 2016
Platform
  1. Windows
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)))
 

Forum statistics

Threads
1,147,747
Messages
5,742,966
Members
423,769
Latest member
LongToast

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top