Piggybacking on another formula - Nested formula question

[NumberMuncher]

Good day,
I have a question regarding nesting a "location" identified in a formula into another formula that is referencing to the same "location"

Background
I have a formula to extract certain details from the description ie A16 to be used in a description in column O
It is a bit long winded since I have to update it for every section where the description changes. I am sure there is a shorter ways to automate but for now this was working well.
I now have to extract additional information into a separate column from the same description as used in the first extraction.
It is easy to create another MID formula however this now causes a double formula adjustment to occur for every segment which is getting more prone to errors and time consumption.

Ideally, I want the Order Number column to “populate” based on the column O identified cell ie $A$16

Without using the Column O formula, I successfully extracted the order number with “=MID($A$16,30,8)”

The problem started when I try to use nested formulas

Here is my step out:

1. MID(A16,30,8) = T361015

1. FORMULATEXT(O17) =CONCATENATE(LEFT($A$16,FIND("(",$A$16,1)-1),A17)

1. MID(FORMULATEXT(O17),19,5) = $A$16

1. MID(MID(FORMULATEXT(O17),19,5),30,8) = ???

Any suggestions on how to do this? Is it even possible?

Thank you

 

[KRice]

If you look down column A, what exactly distinguishes one set of information from another? I see colored entries (red and blue). Do those entries represent the beginning on a related block of information? Do all of the headings have anything in common?...do they all have the word "offer" or are those the only cells where we would find left and right parentheses?

Ideally, I want the Order Number column to “populate” based on the column O identified cell ie $A$16

Can this be generalized? Are you saying that wherever there is a non-black text heading, use that row for the order number?

I see row 21 says Product Code No, but there is no such entry for the product block above it...why is that?

Is it possible to upload a slightly larger sample of your table using the XL2BB add-in? And would you please update your profile to display the version of Excel that you are using?

 

[NumberMuncher]

Nested formula.xlsx
	A	B	C	D	E	F
1	Product Code No.		Order Number	Model No / Product Code
2	Prime/Painted/RHS(正品/喷涂/方管) T361015 offer 187
3	150x50x3.0/8.0m		T361015	Prime/Painted/RHS150x50x3.0/8.0m		=CONCATENATE(LEFT($A$2,FIND("(",$A$2,1)-1),A3)
4	150x50x4.0/8.0m		T361015	Prime/Painted/RHS150x50x4.0/8.0m
5	Sub-total					=MID($A$2,30,8)
6	Prime/Pregal/RHS(正品/镀锌/方管) T361014 offer 187
7	Product Code No.
8	75x75x3.0/8.0m		T361014	Prime/Pregal/RHS75x75x3.0/8.0m		=CONCATENATE(LEFT($A$6,FIND("(",$A$6,1)-1),A8)
9	35x35x3.0/8.0m		T361014	Prime/Pregal/RHS35x35x3.0/8.0m
10	Sub-total					=MID($A$6,30,8)
11	Prime/Painted/RHS(正品/喷涂/方管) T360874 offer 187
12	Product Code No.
13	150x100x4.0/8.0m		T360874	Prime/Painted/RHS150x100x4.0/8.0m
14	Sub-total
15	Prime/Painted/RHS(正品/喷涂/方管) T361006 offer 187
16	Product Code No.
17	75x75x5.0/8.0m		T361006	Prime/Painted/RHS75x75x5.0/8.0m
18	Sub-total
19
20
21						**The coloured lines relates to the product listed between the coloured line and "Sub-total". For further processing this is concatenated to the black product description. I have not been able to find a way to have the formula automatically update for each coloured line - this is corrected manually at present Further details are now required stating the T-number to be listed separately. Rather than manually updating Column C with the MID function - I want to have column C read the location from Column D [$A$2] in the MID function.
22
DLATM 22-305

Cell Formulas
Range	Formula
C3:C4	C3	=MID($A$2,30,8)
D3:D4	D3	=CONCATENATE(LEFT($A$2,FIND("(",$A$2,1)-1),A3)
F3,F8	F3	=FORMULATEXT(D3)
F5,F10	F5	=FORMULATEXT(C3)
C8:C9	C8	=MID($A$6,30,8)
D8:D9	D8	=CONCATENATE(LEFT($A$6,FIND("(",$A$6,1)-1),A8)
C13	C13	=MID($A$11,30,8)
D13	D13	=CONCATENATE(LEFT($A$11,FIND("(",$A$11,1)-1),A13)
C17	C17	=MID($A$15,30,8)
D17	D17	=CONCATENATE(LEFT($A$15,FIND("(",$A$15,1)-1),A17)

 

[NumberMuncher]

If you look down column A, what exactly distinguishes one set of information from another? I see colored entries (red and blue). Do those entries represent the beginning on a related block of information? Do all of the headings have anything in common?...do they all have the word "offer" or are those the only cells where we would find left and right parentheses?

Can this be generalized? Are you saying that wherever there is a non-black text heading, use that row for the order number?

I see row 21 says Product Code No, but there is no such entry for the product block above it...why is that?

Is it possible to upload a slightly larger sample of your table using the XL2BB add-in? And would you please update your profile to display the version of Excel that you are using?

Hi, I have posted a Mini-sheet containing details of what I am looking to achieve

 

[KRice]

Are you familiar with Power Query? I would do this in PQ using an M code script like this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Added Conditional Column" = Table.AddColumn(#"Added Index", "Custom", each if Text.Contains([#"Product Code No."], "(") then [Index] else if [#"Product Code No."] = "Sub-total" then 99999 else if [#"Product Code No."] = "Product Code No." then 99999 else null),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Conditional Column", "Product Code No.", Splitter.SplitTextByDelimiter("(", QuoteStyle.Csv), {"Product Code No..1", "Product Code No..2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Product Code No..2", Splitter.SplitTextByDelimiter(")", QuoteStyle.Csv), {"Product Code No..2.1", "Product Code No..2.2"}),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter1",{{"Product Code No..1", Text.Trim, type text}, {"Product Code No..2.2", Text.Trim, type text}}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Trimmed Text", "Product Code No..2.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Product Code No..2.2.1", "Product Code No..2.2.2"}),
    #"Filled Down1" = Table.FillDown(#"Split Column by Delimiter2",{"Product Code No..2.2.1"}),
    #"Trimmed Text1" = Table.TransformColumns(#"Filled Down1",{{"Product Code No..2.2.1", Text.Trim, type text}, {"Product Code No..2.2.2", Text.Trim, type text}}),
    #"Added Conditional Column1" = Table.AddColumn(#"Trimmed Text1", "Custom.1", each if [Custom] <> null and [Custom] <> 99999 then [#"Product Code No..1"] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column1",{"Custom.1"}),
    #"Added Conditional Column3" = Table.AddColumn(#"Filled Down", "Order Number", each if [Custom] = null then [#"Product Code No..2.2.1"] else null),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column3", "Model / Product Code", each if [Custom] = null then [#"Custom.1"] & " " & [#"Product Code No..1"] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column2",{"Product Code No..2.1", "Product Code No..2.2.1", "Product Code No..2.2.2", "Index", "Custom", "Custom.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Product Code No..1", "Product Code No."}})
in
    #"Renamed Columns"

...which produces output like this:

MrExcel_20220913_NumberMuncher.xlsx
	A	B	C
1	Product Code No.	Order Number	Model / Product Code
2	Prime/Painted/RHS
3	150x50x3.0/8.0m	T361015	Prime/Painted/RHS 150x50x3.0/8.0m
4	150x50x4.0/8.0m	T361015	Prime/Painted/RHS 150x50x4.0/8.0m
5	Sub-total
6	Prime/Pregal/RHS
7	Product Code No.
8	75x75x3.0/8.0m	T361014	Prime/Pregal/RHS 75x75x3.0/8.0m
9	35x35x3.0/8.0m	T361014	Prime/Pregal/RHS 35x35x3.0/8.0m
10	Sub-total
11	Prime/Painted/RHS
12	Product Code No.
13	150x100x4.0/8.0m	T360874	Prime/Painted/RHS 150x100x4.0/8.0m
14	Sub-total
15	Prime/Painted/RHS
16	Product Code No.
17	75x75x5.0/8.0m	T361006	Prime/Painted/RHS 75x75x5.0/8.0m
18	Sub-total
Table1

The file used, which contains the M Code, is available here:

MrExcel_20220913_NumberMuncher.xlsx

Shared with Dropbox

www.dropbox.com

You could try it out with a larger sample of data. Just paste your new information into the existing table and then execute Data > Refresh All > Refresh All (or right click on the results table and choose Refresh).

 

[NumberMuncher]

Hi KRice,
I am familiar with PQ - actually use it on this file but at a later stage once subsequent data has been entered.
That is one reason in particular why I have been hesitant to use PQ earlier. I was not sure how the table would interact with additional data being manually entered. (Newbie that I am)
The additional data is commercial confidence hence the columns not being shown.
However, I am going to try your PQ option and then have the additional data entered on that table which can then be further manipulated with a slight alteration to the existing PQ workings.

Thank you for an excellent answer!

 

[KRice]

You're welcome...I'm happy to help. Let me know if you run into any difficulties. Further edits to the source table should not matter. Once prompted to Refresh, PQ will read the updated table and transform the Product Code No. column. One thing that you will need to do: since the source table in my example is only one column, PQ takes the full table and processes the Product Code No. column. If your actual source table consists of more columns, you have a couple of options:
1. Either keep this query mostly intact and streamlined, so when you read the table into PQ, delete all columns except for the Product Code No. column as the first step in PQ...then the rest of the query should be fine and it will return only three columns, or
2. Keep the full table (or most of it) and add these processing steps to the other ones that are part of the transformations done later.