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

By "next to" do you mean to the right of or left of that cell?

You didn't say where you want the result. If in column A, then try this in A2 and fill down:

Code:

=IFERROR(INDEX(B2:V2,,1+MATCH("'parcel_volume':",B2:V2,0)),"")

Do you also want the trailing comma?

Kyleth1407 · Jun 3, 2023

kweaver said:
By "next to" do you mean to the right of or left of that cell?

You didn't say where you want the result. If in column A, then try this in A3 and fill down:

Code:

=IFERROR(INDEX(B3:V3,,1+MATCH("'parcel_volume':",B3:V3,0)),"")

Do you also want the trailing comma?

Hi sorry I want the next cell to the right and return the value on another sheet. thank you

Peter_SSs · Jun 3, 2023

Welcome to the MrExcel board!

See if this is what you want. I've hidden some columns.

23 06 04.xlsm

B

C

D

L

M

N

O

P

Q

R

S

T

U

V

W

1

Unit

Extra

parcel_volume':

parcel_volume

2

{'jfo_no':

'3033182041',

'cradle_uid':

'real_weight':

395000.0,

'parcel_volume':

2622000.0,

'packing_id':

None,

'shipment_uid':

'182041',

'pallet_space':

2}

2622000.0,

3

{'jfo_no':

'3033182038',

'cradle_uid':

'real_weight':

395000.0,

'parcel_volume':

2622000.0,

'packing_id':

None,

'shipment_uid':

'182038',

'pallet_space':

2}

2622000.0,

4

{'jfo_no':

'3723154206',

'cradle_uid':

'real_weight':

268000.0,

'parcel_volume':

2453000.0,

2}

2453000.0,

5

{'jfo_no':

'3723154148',

'cradle_uid':

None,

'real_weight':

290000.0,

'parcel_volume':

2560000.0,

'packing_id':

'Y11',

'shipment_uid':

'154148',

'pallet_space':

2560000.0,

6

{'jfo_no':

'3723154121',

'cradle_uid':

'real_weight':

293000.0,

'parcel_volume':

2870000.0,

'packing_id':

None,

'shipment_uid':

'154121',

'pallet_space':

2}

2870000.0,

7

{'jfo_no':

'3723154191',

'cradle_uid':

'real_weight':

245000.0,

'parcel_volume':

2061999.9999999998,

2}

2061999.9999999998,

8

{'jfo_no':

'3723154096',

'cradle_uid':

None,

'real_weight':

290000.0,

'parcel_volume':

2560000.0,

'packing_id':

'Y11',

'shipment_uid':

'154096',

'pallet_space':

2560000.0,

Sheet2 (2)

Cell Formulas
Range	Formula
W2:W8	W2	=INDEX(C2:U2,MATCH(""&W$1&"",B2:T2,0))

Kyleth1407 · Jun 3, 2023

kweaver said:
By "next to" do you mean to the right of or left of that cell?

You didn't say where you want the result. If in column A, then try this in A3 and fill down:

Code:

=IFERROR(INDEX(B3:V3,,1+MATCH("'parcel_volume':",B3:V3,0)),"")

Do you also want the trailing comma?

and no I am looking for the number not showing decimal place or comma thank you

Peter_SSs · Jun 3, 2023

Kyleth1407 said:
I am looking for the number not showing decimal place or comma

So, what is your wanted result from row 7?

Peter_SSs · Jun 3, 2023

Peter_SSs said:
So, what is your wanted result from row 7?

I'm guessing this?

23 06 04.xlsm

L

M

N

O

P

Q

V

W

1

parcel_volume':

parcel_volume

2

'real_weight':

395000.0,

'parcel_volume':

2622000.0,

'packing_id':

None,

2622000

3

'real_weight':

395000.0,

'parcel_volume':

2622000.0,

'packing_id':

None,

2622000

4

'real_weight':

268000.0,

'parcel_volume':

2453000.0,

2453000

5

None,

'real_weight':

290000.0,

'parcel_volume':

2560000.0,

'packing_id':

2560000

6

'real_weight':

293000.0,

'parcel_volume':

2870000.0,

'packing_id':

None,

2870000

7

'real_weight':

245000.0,

'parcel_volume':

2061999.9999999998,

2062000

8

None,

'real_weight':

290000.0,

'parcel_volume':

2560000.0,

'packing_id':

2560000

parcel_volume

Cell Formulas
Range	Formula
W2:W8	W2	=ROUND(SUBSTITUTE(INDEX(C2:U2,MATCH(""&W$1&"",B2:T2,0)),",",""),0)

kweaver · Jun 3, 2023

How about this (without a helper column):

Code:

=IFERROR(INT(0+LEFT(INDEX(B2:V2,,1+MATCH("'parcel_volume':",B2:V2,0)),LEN(INDEX(B2:V2,,1+MATCH("'parcel_volume':",B2:V2,0)))-1)),"")

kweaver · Jun 3, 2023

Peter_SSs said:
So, what is your wanted result from row 7?

good question.

Peter_SSs · Jun 3, 2023

kweaver said:
(without a helper column)

What helper column are you referring to?

Formula Help

Kyleth1407

New Member

Excel Facts

kweaver

Well-known Member

Kyleth1407

New Member

Peter_SSs

MrExcel MVP, Moderator

Kyleth1407

New Member

Peter_SSs

MrExcel MVP, Moderator

Peter_SSs

MrExcel MVP, Moderator

kweaver

Well-known Member

kweaver

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Forum statistics

Share this page

Formula Help

New Member

Excel Facts

Well-known Member

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

MrExcel MVP, Moderator

Well-known Member

Well-known Member

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