Formula Help

Kyleth1407 · Jun 3, 2023

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

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

1

Unit

Extra

parcel_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/MT07A

2023',

'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/MT07A

2023',

'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//PW50

2023

DPBSE',

'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//PW50

2023

DPBSE',

'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/XS700

2022',

'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//YFM450FWB

20',

'support_no':

None,

16

{'jfo_no':

'3723154316',

'cradle_uid':

None,

'cmr':

'50342444',

'consignor_org_id':

'5Y4AJ86W0P0502727//YFM450FWB

2023',

'support_no':

None,

'real_weight':

363000.0,

'parcel_volume':

2580000.0,

17

{'jfo_no':

'3723154313',

'cradle_uid':

None,

'cmr':

'50342433',

'consignor_org_id':

'5Y4AJ87W0L0500152//YFM450FWB

CAMO

2',

'support_no':

None,

'real_weight':

363000.0,

'parcel_volume':

18

{'jfo_no':

'3723154315',

'cradle_uid':

None,

'cmr':

'50342466',

'consignor_org_id':

'5Y4AJ86W0P0502726//YFM450FWB

2023',

'support_no':

None,

'real_weight':

363000.0,

'parcel_volume':

2580000.0,

19

{'jfo_no':

'3723154314',

'cradle_uid':

None,

'cmr':

'50342455',

'consignor_org_id':

'5Y4AJ86W0P0502676//YFM450FWB

2023',

'support_no':

None,

'real_weight':

363000.0,

'parcel_volume':

2580000.0,

20

{'jfo_no':

'3723154311',

'cradle_uid':

None,

'cmr':

'50342422',

'consignor_org_id':

'5Y4AJ87W0L0500161//YFM450FWB

CAMO

2',

'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/MT07A

2023',

'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/MT07A

2023',

'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/MT07A

2023',

'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/MT07A

2023',

'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-R125A

2',

'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/MT125A

2023',

'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

kweaver · Jun 3, 2023

Peter_SSs said:
What helper column are you referring to?

I mean I didn't use a helper column with that long formula.

Peter_SSs · Jun 3, 2023

kweaver said:
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.

kweaver · Jun 3, 2023

Peter_SSs said:
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.

I realize...just thought my long formula would have been better with a helper column.

Peter_SSs · Jun 3, 2023

Ah, I see.

Kyleth1407 · Jun 3, 2023

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.

Peter_SSs · Jun 3, 2023

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

N

O

P

Q

V

W

1

parcel_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

5

290000.0,

'parcel_volume':

2560000.0,

'packing_id':

2560000

6

'parcel_volume':

2870000.0,

'packing_id':

None,

2870000

7

'parcel_volume':

2061999.9999999998,

2061999

8

290000.0,

'parcel_volume':

2560000.0,

'packing_id':

2560000

parcel_volume

Cell Formulas
Range	Formula
W2:W8	W2	=IFNA(INT(SUBSTITUTE(INDEX(C2:U2,MATCH("'parcel_volume':",B2:T2,0)),",","")),"")

Formula Help

Kyleth1407

New Member

kweaver

Well-known Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

kweaver

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Kyleth1407

New Member

Peter_SSs

MrExcel MVP, Moderator

Forum statistics

Share this page

Formula Help

Kyleth1407

New Member

kweaver

Well-known Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

kweaver

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Kyleth1407

New Member

Peter_SSs

MrExcel MVP, Moderator

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock