Formula Help

Kyleth1407

New Member
Joined
Jun 3, 2023
Messages
4
Office Version
  1. 2010
Platform
  1. Windows
Hi all, im a bit stuck with this.

I am trying to find a formula that will find the 'parcel_volume': cell and then return the cell next to it. But as you can see below that 'parcel_volume': is not just in colulm N and is somtimes in another colulmn. I am unable to change the layout of this as it is exported from a 3rd party export.

In enssance I want the number in the cell next to 'parcel_volume': as I am trying to make invoicing easier for me.

Thank you in advance


TEST.xlsx
BCDEFGHIJKLMNOPQRSTU
1UnitExtraparcel_volume':
2{'jfo_no':'3033182041','cradle_uid':'0','cmr':'PL1734312','consignor_org_id':'PL1734312','support_no':None,'real_weight':395000.0,'parcel_volume':2622000.0,'packing_id':None,'shipment_uid':'182041','pallet_space':2}
3{'jfo_no':'3033182038','cradle_uid':'0','cmr':'PL1734311','consignor_org_id':'PL1734311','support_no':None,'real_weight':395000.0,'parcel_volume':2622000.0,'packing_id':None,'shipment_uid':'182038','pallet_space':2}
4{'jfo_no':'3723154206','cradle_uid':None,'cmr':'JS1EM12AZP7101174','consignor_org_id':'0138121','support_no':None,'real_weight':268000.0,'parcel_volume':2453000.0,2}
5{'jfo_no':'3723154148','cradle_uid':'1100020241','cmr':'50324635','consignor_org_id':'JYARM339000002622/1100020241/MT07A2023','support_no':None,'real_weight':290000.0,'parcel_volume':2560000.0,'packing_id':'Y11','shipment_uid':'154148','pallet_space':
6{'jfo_no':'3723154121','cradle_uid':None,'cmr':'JKALZT00CEA030750','consignor_org_id':'JKALZT00CEA030750','support_no':None,'real_weight':293000.0,'parcel_volume':2870000.0,'packing_id':None,'shipment_uid':'154121','pallet_space':2}
7{'jfo_no':'3723154191','cradle_uid':None,'cmr':'JS1C733MZP7106133','consignor_org_id':'0137685','support_no':None,'real_weight':245000.0,'parcel_volume':2061999.9999999998,2}
8{'jfo_no':'3723154096','cradle_uid':'1100021255','cmr':'50324646','consignor_org_id':'JYARM339000002796/1100021255/MT07A2023','support_no':None,'real_weight':290000.0,'parcel_volume':2560000.0,'packing_id':'Y11','shipment_uid':'154096','pallet_space':
9{'jfo_no':'3033182356','cradle_uid':'7311130','cmr':'PL1738317','consignor_org_id':'PL1738317','support_no':None,'real_weight':1043000.0,'parcel_volume':7473000.0,'packing_id':None,'shipment_uid':'182356','pallet_space':5}
10{'jfo_no':'3033182423','cradle_uid':'7310412','cmr':'PL1738341','consignor_org_id':'PL1738341','support_no':None,'real_weight':1043000.0,'parcel_volume':7473000.0,'packing_id':None,'shipment_uid':'182423','pallet_space':5}
11{'jfo_no':'3723154215','cradle_uid':None,'cmr':'50314882','consignor_org_id':'JYA3PT00000595169//PW502023DPBSE','support_no':None,'real_weight':140000.0,'parcel_volume':680000.0,'packing_id':None,'shipment_uid':'154215',
12{'jfo_no':'3723154214','cradle_uid':None,'cmr':'50314871','consignor_org_id':'JYA3PT00000595184//PW502023DPBSE','support_no':None,'real_weight':140000.0,'parcel_volume':680000.0,'packing_id':None,'shipment_uid':'154214',
13{'jfo_no':'3723154213','cradle_uid':'1500048143','cmr':'50311135','consignor_org_id':'VG5RM364000004781/1500048143/XS7002022','support_no':None,'real_weight':236000.0,'parcel_volume':2680000.0,'packing_id':'Y15','shipment_uid':'154213','pallet_space':
14{'jfo_no':'3723154618','cradle_uid':None,'cmr':'JS1EK12AZP7102232','consignor_org_id':'0138148','support_no':None,'real_weight':266000.0,'parcel_volume':2095000.0000000002,2}
15{'jfo_no':'3723154312','cradle_uid':None,'cmr':'50342411','consignor_org_id':'5Y4AJ86W0L0501204//YFM450FWB20','support_no':None,
16{'jfo_no':'3723154316','cradle_uid':None,'cmr':'50342444','consignor_org_id':'5Y4AJ86W0P0502727//YFM450FWB2023','support_no':None,'real_weight':363000.0,'parcel_volume':2580000.0,
17{'jfo_no':'3723154313','cradle_uid':None,'cmr':'50342433','consignor_org_id':'5Y4AJ87W0L0500152//YFM450FWBCAMO2','support_no':None,'real_weight':363000.0,'parcel_volume':
18{'jfo_no':'3723154315','cradle_uid':None,'cmr':'50342466','consignor_org_id':'5Y4AJ86W0P0502726//YFM450FWB2023','support_no':None,'real_weight':363000.0,'parcel_volume':2580000.0,
19{'jfo_no':'3723154314','cradle_uid':None,'cmr':'50342455','consignor_org_id':'5Y4AJ86W0P0502676//YFM450FWB2023','support_no':None,'real_weight':363000.0,'parcel_volume':2580000.0,
20{'jfo_no':'3723154311','cradle_uid':None,'cmr':'50342422','consignor_org_id':'5Y4AJ87W0L0500161//YFM450FWBCAMO2','support_no':None,'real_weight':363000.0,'parcel_volume':
21{'jfo_no':'3723154639','cradle_uid':'9015190','cmr':'2SH107682','consignor_org_id':'BM0992','support_no':None,'real_weight':307000.0,'parcel_volume':2500000.0,2}
22{'jfo_no':'3723154655','cradle_uid':'7908237','cmr':'2SH107864','consignor_org_id':'BL4994,BL7411','support_no':None,'real_weight':342000.0,'parcel_volume':2500000.0,'packing_id':'ST','shipment_uid':'154655','pallet_space':2}
23{'jfo_no':'3723154655','cradle_uid':'9026229','cmr':'2SH107864','consignor_org_id':'BL4994,BL7411','support_no':None,'real_weight':308000.0,'parcel_volume':2500000.0,'packing_id':'ST','shipment_uid':'154655','pallet_space':2}
24{'jfo_no':'3723154631','cradle_uid':None,'cmr':'2SH107683','consignor_org_id':'BK3368','support_no':None,'real_weight':302000.0,'parcel_volume':2500000.0,2}
25{'jfo_no':'3033182466','cradle_uid':'8410082','cmr':'PL1738313','consignor_org_id':'PL1738313','support_no':None,'real_weight':1122000.0,'parcel_volume':9306000.0,'packing_id':None,'shipment_uid':'182466','pallet_space':7}
26{'jfo_no':'3723153920','cradle_uid':'7901497','cmr':'2SH107331','consignor_org_id':'BL0913,BJ9327,BK1988,BL4136,BH0046','support_no':None,'real_weight':339000.0,'parcel_volume':2500000.0,2}
27{'jfo_no':'3723153920','cradle_uid':'9023105','cmr':'2SH107331','consignor_org_id':'BL0913,BJ9327,BK1988,BL4136,BH0046','support_no':None,'real_weight':335000.0,'parcel_volume':2500000.0,2}
28{'jfo_no':'3723153920','cradle_uid':'9025651','cmr':'2SH107331','consignor_org_id':'BL0913,BJ9327,BK1988,BL4136,BH0046','support_no':None,'real_weight':280000.0,'parcel_volume':2500000.0,2}
29{'jfo_no':'3723153920','cradle_uid':'9020025','cmr':'2SH107331','consignor_org_id':'BL0913,BJ9327,BK1988,BL4136,BH0046','support_no':None,'real_weight':275000.0,'parcel_volume':2500000.0,2}
30{'jfo_no':'3723153920','cradle_uid':'9018276','cmr':'2SH107331','consignor_org_id':'BL0913,BJ9327,BK1988,BL4136,BH0046','support_no':None,'real_weight':272000.0,'parcel_volume':2500000.0,2}
31{'jfo_no':'3033182465','cradle_uid':'8410048','cmr':'PL1738312','consignor_org_id':'PL1738312','support_no':None,'real_weight':1122000.0,'parcel_volume':9306000.0,'packing_id':None,'shipment_uid':'182465','pallet_space':7}
32{'jfo_no':'3723154496','cradle_uid':'9003118','cmr':'2SH108136','consignor_org_id':'BL7483','support_no':None,'real_weight':270000.0,'parcel_volume':2500000.0,2}
33{'jfo_no':'3723154491','cradle_uid':'9006128','cmr':'2SH108143','consignor_org_id':'BL7612','support_no':None,'real_weight':270000.0,'parcel_volume':2500000.0,'packing_id':'ST','shipment_uid':'154491','pallet_space':2}
34{'jfo_no':'3723154684','cradle_uid':None,'cmr':'MH4LX110CCJP01542','consignor_org_id':'MH4LX110CCJP01542','support_no':None,'real_weight':100000.0,'parcel_volume':807000.0,1}
35{'jfo_no':'3723154887','cradle_uid':None,'cmr':'05003F-342232','consignor_org_id':'0041935','support_no':None,'real_weight':141000.0,'parcel_volume':934000.0,'packing_id':None,'shipment_uid':'154887','pallet_space':1}
36{'jfo_no':'3723154516','cradle_uid':'1100001230','cmr':'50325208','consignor_org_id':'JYARM339000002813/1100001230/MT07A2023','support_no':None,'real_weight':290000.0,'parcel_volume':2560000.0,'packing_id':'Y11','shipment_uid':'154516','pallet_space':
37{'jfo_no':'3723154775','cradle_uid':None,'cmr':'JKBKX252CCA026398','consignor_org_id':'JKBKX252CCA026398','support_no':None,'real_weight':142000.0,'parcel_volume':1288000.0,1}
38{'jfo_no':'3723154774','cradle_uid':None,'cmr':'JKBKX252CCA026333','consignor_org_id':'JKBKX252CCA026333','support_no':None,'real_weight':142000.0,'parcel_volume':1288000.0,1}
39{'jfo_no':'3723154513','cradle_uid':'1100022520','cmr':'50325265','consignor_org_id':'JYARM339000002809/1100022520/MT07A2023','support_no':None,'real_weight':290000.0,'parcel_volume':2560000.0,'packing_id':'Y11','shipment_uid':'154513','pallet_space':
40{'jfo_no':'3723154508','cradle_uid':'1100031843','cmr':'50324927','consignor_org_id':'JYARM339000002763/1100031843/MT07A2023','support_no':None,'real_weight':290000.0,'parcel_volume':'pallet_space':
41{'jfo_no':'3723154512','cradle_uid':'1100020645','cmr':'50324376','consignor_org_id':'JYARM339000002820/1100020645/MT07A2023','support_no':None,'real_weight':290000.0,'parcel_volume':2560000.0,'packing_id':'Y11','shipment_uid':'154512','pallet_space':
42{'jfo_no':'3723154901','cradle_uid':None,'cmr':'MB8EA12LZR8100195','consignor_org_id':'0137973','support_no':None,'real_weight':142000.0,'parcel_volume':1872000.0,2}
43{'jfo_no':'3723154652','cradle_uid':'9026301','cmr':'2SH107690','consignor_org_id':'BM0470,BL5344','support_no':None,'real_weight':320000.0,'parcel_volume':2500000.0,'packing_id':'ST','shipment_uid':'154652','pallet_space':2}
44{'jfo_no':'3723154652','cradle_uid':'9021161','cmr':'2SH107690','consignor_org_id':'BM0470,BL5344','support_no':None,'real_weight':304000.0,'parcel_volume':2500000.0,'packing_id':'ST','shipment_uid':'154652','pallet_space':2}
45{'jfo_no':'3723154716','cradle_uid':'1500030754','cmr':'50341691','consignor_org_id':'VG5RE406000000637/1500030754/YZF-R125A2','support_no':None,'real_weight':227000.0,'parcel_volume':2680000.0,'packing_id':'Y15','shipment_uid':'154716','pallet_space':
46{'jfo_no':'3723154712','cradle_uid':'1590019446','cmr':'50341768','consignor_org_id':'VG5RE407000000695/1590019446/MT125A2023','support_no':None,'real_weight':222000.0,'parcel_volume':2680000.0,'packing_id':'Y15','shipment_uid':'154712','pallet_space':
47{'jfo_no':'3723155091','cradle_uid':'9023826','cmr':'2SH108219','consignor_org_id':'BM5538','support_no':None,'real_weight':279000.0,'parcel_volume':2500000.0,'packing_id':'ST','shipment_uid':'155091','pallet_space':2}
48{'jfo_no':'3723155050','cradle_uid':None,'cmr':'WB10M0102P-6H60134-0','consignor_org_id':'WB10M0102P-6H60134-CA','support_no':None,'real_weight':250000.0,'parcel_volume':2574000.0,'packing_id':'BMW','shipment_uid':'155050','pallet_space':2}
49{'jfo_no':'3723155098','cradle_uid':None,'cmr':'WB10E8102P-6H59173-0','consignor_org_id':'WB10E8102P-6H59173-CA','support_no':None,'real_weight':250000.0,'parcel_volume':2574000.0,'packing_id':'BMW','shipment_uid':'155098','pallet_space':2}
50{'jfo_no':'3723155094','cradle_uid':None,'cmr':'WB10E8109P-6H58991-0','consignor_org_id':'WB10E8109P-6H58991-CA','support_no':None,'real_weight':250000.0,'parcel_volume':2574000.0,'packing_id':'BMW','shipment_uid':'155094','pallet_space':2}
51{'jfo_no':'3723154659','cradle_uid':'9020926','cmr':'2SH107865','consignor_org_id':'BK5219,BL5943,BL8222,BL7955','support_no':None,'real_weight':319000.0,'parcel_volume':2500000.0,2}
52{'jfo_no':'3723154659','cradle_uid':'9026188','cmr':'2SH107865','consignor_org_id':'BK5219,BL5943,BL8222,BL7955','support_no':None,'real_weight':308000.0,'parcel_volume':2500000.0,2}
53{'jfo_no':'3723154659','cradle_uid':'9018761','cmr':'2SH107865','consignor_org_id':'BK5219,BL5943,BL8222,BL7955','support_no':None,'real_weight':288000.0,'parcel_volume':2500000.0,2}
54{'jfo_no':'3723154659','cradle_uid':'9008658','cmr':'2SH107865','consignor_org_id':'BK5219,BL5943,BL8222,BL7955','support_no':None,'real_weight':266000.0,'parcel_volume':2500000.0,2}
55{'jfo_no':'3723154869','cradle_uid':'9003178','cmr':'2SH108053','consignor_org_id':'BG7587','support_no':None,'real_weight':296000.0,'parcel_volume':2500000.0,'packing_id':'ST','shipment_uid':'154869','pallet_space':2}
Sheet1
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
I mean I didn't use a helper column with that long formula.
Fair enough. It's just that you didn't use a helper column with the shorter formula either and in fact none of the suggestions have used a helper column, so I was unsure why you mentioned it. :unsure:
 
Upvote 0
Fair enough. It's just that you didn't use a helper column with the shorter formula either and in fact none of the suggestions have used a helper column, so I was unsure why you mentioned it. :unsure:
I realize...just thought my long formula would have been better with a helper column.
 
Upvote 0
Thank you both for your help it has worked miracles and now it has made invoiceing much easier for me as it is now pricing automatically. Unfortantly I am unable to post the full sheet as there is confedential data on the spreadsheet, but again thank you for your help.
 
Upvote 0
You didn't answer my question about row 7 but I noticed that you marked post #8 as the solution so you must want 2061999 for that row not 2062000 as per my formula.
If interested, here is a considerably shorter one that I believe does the same job as post #8

23 06 04.xlsm
NOPQVW
1parcel_volume':parcel_volume
2'parcel_volume':2622000.0,'packing_id':None,2622000
3'parcel_volume':2622000.0,'packing_id':None,2622000
4'parcel_volume':2453000.0,2453000
5290000.0,'parcel_volume':2560000.0,'packing_id':2560000
6'parcel_volume':2870000.0,'packing_id':None,2870000
7'parcel_volume':2061999.9999999998,2061999
8290000.0,'parcel_volume':2560000.0,'packing_id':2560000
parcel_volume
Cell Formulas
RangeFormula
W2:W8W2=IFNA(INT(SUBSTITUTE(INDEX(C2:U2,MATCH("'parcel_volume':",B2:T2,0)),",","")),"")
 
Upvote 0

Forum statistics

Threads
1,223,997
Messages
6,175,874
Members
452,679
Latest member
darryl47nopra

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