Recursive String Extraction from a range and Computation

airforceone

airforceone

Board Regular
Joined
Feb 14, 2022
Messages
177
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
before posting this question, i read almost all that is related to string extraction procedure but none could ease my weeklong headache :)
i tried the following formula (Row 9 - 13) and got that so far but what is needed is to scan vitamins (field/column) and separate different Vitamins and ADD the total (Vitamins Type) and SUM for all particular Branch Control Number (BCN) in a row as shown in Row A16

Help Needed.xlsx
ABCDEFGHIJKLMN
1Branch Control NumberVitamins
2A00001-2022-0001Vit A Grams : 0.006 Value : 1000
3A00001-2022-0002Vit A Grams : 0.006 Value : 1000,Vit B Grams : 0.008 Value : 2000
4A00001-2022-0003Vit D Grams : 0.006 Value : 1000,Vit B Grams : 0.008 Value : 2000,Vit C Grams : 0.010 Value : 3002
5A00001-2022-0004Zinc Grams : 0.006 Value : 1000,Vit B Grams : 0.008 Value : 2000,Vit D Grams : 0.010 Value : 3003
6A00001-2022-0005Vit A Grams : 0.006 Value : 1000,Vit A Grams : 0.5 Value : 600,Zinc Grams : 0.008 Value : 2000,Vit D Grams : 0.010 Value : 3004
7
8Tried FormulaRESULT
9=IF(ISERR(FIND(",",B6)),B6,MID(B6,FIND(",",B6,1)+1,99))Vit A Grams : 0.5 Value : 600,Zinc Grams : 0.008 Value : 2000,Vit D Grams : 0.010 Value : 3004
10=LEFT(B6, SEARCH(",",B6)-1)Vit A Grams : 0.006 Value : 1000
11=LEFT(F9, SEARCH(",",F9)-1)Vit A Grams : 0.5 Value : 600
12=IF(ISERR(FIND(",",F9)),F9,MID(F9,FIND(",",F9,1)+1,99))Zinc Grams : 0.008 Value : 2000,Vit D Grams : 0.010 Value : 3004
13=RIGHT(F9, SEARCH(",",F9)+2)Vit D Grams : 0.010 Value : 3004
14
15Expected ResultVit A (2 Entry)ZincVit DTotal
16A00001-2022-0005 (Row A6)0.5060.0080.010.524
17
18NOTE: Branch Control Number may contain multiple pack of vitamins (up to 20)
Sheet1
Cell Formulas
RangeFormula
F9F9=IF(ISERR(FIND(",",B6)),B6,MID(B6,FIND(",",B6,1)+1,99))
F10F10=LEFT(B6, SEARCH(",",B6)-1)
F11F11=LEFT(F9, SEARCH(",",F9)-1)
F12F12=IF(ISERR(FIND(",",F9)),F9,MID(F9,FIND(",",F9,1)+1,99))
F13F13=RIGHT(F9, SEARCH(",",F9)+2)
B16B16=0.006+0.5
E16E16=SUM(B16:E16)
 

Attachments

  • 2022.02.14 - Help Pix.JPG
    2022.02.14 - Help Pix.JPG
    255 KB · Views: 7

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
Welcome to the MrExcel forum!

You could do something like this:

Book1 (version 1).xlsb
ABCDEFG
1Branch Control NumberVitamins
2A00001-2022-0001Vit A Grams : 0.006 Value : 1000
3A00001-2022-0002Vit A Grams : 0.006 Value : 1000,Vit B Grams : 0.008 Value : 2000
4A00001-2022-0003Vit D Grams : 0.006 Value : 1000,Vit B Grams : 0.008 Value : 2000,Vit C Grams : 0.010 Value : 3002
5A00001-2022-0004Zinc Grams : 0.006 Value : 1000,Vit B Grams : 0.008 Value : 2000,Vit D Grams : 0.010 Value : 3003
6A00001-2022-0005Vit A Grams : 0.006 Value : 1000,Vit A Grams : 0.500 Value : 600,Zinc Grams : 0.008 Value : 2000,Vit D Grams : 0.010 Value : 3004
7
8
9Expected ResultVit AVit BVit CVit DZincTotal
10A00001-2022-00010.00600000.006
11A00001-2022-00020.0060.0080000.014
12A00001-2022-000300.0080.010.00600.024
13A00001-2022-000400.00800.010.0060.024
14A00001-2022-00050.506000.010.0080.524
Sheet15
Cell Formulas
RangeFormula
A10:A14A10=A2
B10:F14B10=SUM(IF(MID($B2,ROW(INDIRECT("1:"&LEN($B2))),LEN(B$9))=B$9,MID($B2,ROW(INDIRECT("1:"&LEN($B2)))+LEN(B$9)+9,6)+0,0))
G10:G14G10=SUM(B10:F10)
Press CTRL+SHIFT+ENTER to enter array formulas.


Notice that on the B6 row, I changed 0.5 to 0.500. If that's not an option, I can adapt the formula, but the complexity shoots up a lot. This task is probably better suited to a UDF.
 
Upvote 0
Eric W said:
Welcome to the MrExcel forum!

You could do something like this:

Book1 (version 1).xlsb
ABCDEFG
1Branch Control NumberVitamins
2A00001-2022-0001Vit A Grams : 0.006 Value : 1000
3A00001-2022-0002Vit A Grams : 0.006 Value : 1000,Vit B Grams : 0.008 Value : 2000
4A00001-2022-0003Vit D Grams : 0.006 Value : 1000,Vit B Grams : 0.008 Value : 2000,Vit C Grams : 0.010 Value : 3002
5A00001-2022-0004Zinc Grams : 0.006 Value : 1000,Vit B Grams : 0.008 Value : 2000,Vit D Grams : 0.010 Value : 3003
6A00001-2022-0005Vit A Grams : 0.006 Value : 1000,Vit A Grams : 0.500 Value : 600,Zinc Grams : 0.008 Value : 2000,Vit D Grams : 0.010 Value : 3004
7
8
9Expected ResultVit AVit BVit CVit DZincTotal
10A00001-2022-00010.00600000.006
11A00001-2022-00020.0060.0080000.014
12A00001-2022-000300.0080.010.00600.024
13A00001-2022-000400.00800.010.0060.024
14A00001-2022-00050.506000.010.0080.524
Sheet15
Cell Formulas
RangeFormula
A10:A14A10=A2
B10:F14B10=SUM(IF(MID($B2,ROW(INDIRECT("1:"&LEN($B2))),LEN(B$9))=B$9,MID($B2,ROW(INDIRECT("1:"&LEN($B2)))+LEN(B$9)+9,6)+0,0))
G10:G14G10=SUM(B10:F10)
Press CTRL+SHIFT+ENTER to enter array formulas.


Notice that on the B6 row, I changed 0.5 to 0.500. If that's not an option, I can adapt the formula, but the complexity shoots up a lot. This task is probably better suited to a UDF.
Click to expand...
Eric W said:
Welcome to the MrExcel forum!

You could do something like this:

Book1 (version 1).xlsb
ABCDEFG
1Branch Control NumberVitamins
2A00001-2022-0001Vit A Grams : 0.006 Value : 1000
3A00001-2022-0002Vit A Grams : 0.006 Value : 1000,Vit B Grams : 0.008 Value : 2000
4A00001-2022-0003Vit D Grams : 0.006 Value : 1000,Vit B Grams : 0.008 Value : 2000,Vit C Grams : 0.010 Value : 3002
5A00001-2022-0004Zinc Grams : 0.006 Value : 1000,Vit B Grams : 0.008 Value : 2000,Vit D Grams : 0.010 Value : 3003
6A00001-2022-0005Vit A Grams : 0.006 Value : 1000,Vit A Grams : 0.500 Value : 600,Zinc Grams : 0.008 Value : 2000,Vit D Grams : 0.010 Value : 3004
7
8
9Expected ResultVit AVit BVit CVit DZincTotal
10A00001-2022-00010.00600000.006
11A00001-2022-00020.0060.0080000.014
12A00001-2022-000300.0080.010.00600.024
13A00001-2022-000400.00800.010.0060.024
14A00001-2022-00050.506000.010.0080.524
Sheet15
Cell Formulas
RangeFormula
A10:A14A10=A2
B10:F14B10=SUM(IF(MID($B2,ROW(INDIRECT("1:"&LEN($B2))),LEN(B$9))=B$9,MID($B2,ROW(INDIRECT("1:"&LEN($B2)))+LEN(B$9)+9,6)+0,0))
G10:G14G10=SUM(B10:F10)
Press CTRL+SHIFT+ENTER to enter array formulas.


Notice that on the B6 row, I changed 0.5 to 0.500. If that's not an option, I can adapt the formula, but the complexity shoots up a lot. This task is probably better suited to a UDF.
Click to expand...
sorry for the late reply, the formula evaluated to 0. i think it is really suited for UDF! anyway I'll just post a new question.... and hope you can help on that also cheers mate...
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Extracting last name from full name
2
Replies
16
Views
792
bearcub
B
M
  • Question
Extract data from Word to Excel
Replies
1
Views
1K
MHamid
M
MrDB4Excel
  • Solved
If Exist in a Column Range
Replies
8
Views
895
DanteAmor
DanteAmor
C
  • Question
Extracting Dates and Amounts from Raw Export
Replies
1
Views
510
copyboy007
C
C
  • Question
Extracting specific text from text string
Replies
8
Views
2K
Fluff
Fluff

Forum statistics

Threads
1,214,606
Messages
6,120,483
Members
448,967
Latest member
visheshkotha

Share this page

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
Back
Top