complicated sort tire size for each part based on structure into another sheet

A

abdo meghari

Well-known Member
Joined
Aug 3, 2021
Messages
651
Office Version
  1. 2019
hello
I would sort tires size for each part into sheet In & Out Balance based on structure into sheet list. the tires sizes are not arranged . when rearrange should see category for each part contain specific tires size should be matched in column A beteween two sheets . also there are some tires sizes are not existed in sheet In & Out Balance but they are existed in sheet list then should add to the part is matched between two sheets in column A and when add it should be the same borders and formatting also formulas
the category in column A for each part starts from the first row and finish to row TTL in sheet In & Out Balance as to sheet list category in column A for each part starts from the first row and finish to start new category so I highlighted the ne tires are existed in sheet list and how should add in sheet In & Out Balance
note: the data will increase in two sheets with the same structure . also to understand how sort data in sheet In & Out Balance should see three syllabels from small to big
for instance 175/70R13 should sort based on 175 , /70 , R13
also or 195R14C 195/70R15C when sort from small to big should be tires size below which doesn't contain letter C in the end size .
sort .xls
ABCDEFGHIJKLM
2CategorySizePatternOriginArrivedSalesStock Arrived SalesStock Arrived SalesStock
3PSR (LRD)185/65R14TECTHI--- - - - - - -
4185/65R14EP150INDO--- - - - - - -
5 185/65R14150EZINDO
6185/65R15TC10INDO10--
7185/65R15T005INDO--- - - - - - -
8185/65R15T01JAP
9185/65R15B250JAP
10175/70R13B25INDO161845179179
11175/70R13EP150THI---------
12185/70R13EP150INDO---------
13175/65R14EP150INDO---------
14175/70R14EP150THI - - - ------
15TTL-17184-5179--179
16PSR (HRD)215/45R17T001JAP---------
17215/50R17EP300THI---------
18215/55R17GR90INDO--- - - - - - -
19215/55R17T001JAP--- - - - - - -
20215/55R17T005JAP
21TTL---------
22LTR195R14C613VJAP---------
23195R14CR623THI
24205R14C613VJAP-444
25205R14CR624INDO
26650R16R23082198211211
27700R16R23012PR31611931351,058177111,224
28750R16R2301,22191051408,9654,46851312,920
29155R12CR624INDO---------
30155R12CR623INDO---------
31165R13CR624INDO---------
32185R14C613VJAP8252525
33185R14CR624INDO-3014297297
34195R14CR624INDO---------
35TTL-1,55310,847-28710,5604,64552414,681
36TBR1200R20 G580 JAP3,5702,6411,653988787930845
371200R20 R187JAP607481418633330
381200R20 G580THI
391200R20 R187THI
40TTL-4,1773,122-2,0711,051787963875
I[B
n & Out Balance[/B]|tw:766]
Cell Formulas
RangeFormula
J6,M36:M37,J36:J37,M32:M33,J32:J33,M26:M28,J26:J28,M24,J24,M10:M14,J10:J13,M6J6=G6+H6-I6
E15:M15E15=SUM(E3:E14)
E21:M21E21=SUM(E16:E20)
F27F27=310+6
E35:M35E35=SUM(E22:E34)
F37F37=586+21
E40:M40E40=SUM(E36:E39)



sort .xls
ABCD
2CategorySizePatternOrigin
3PSR (LRD)175/70R13B25INDO
4175/70R13EP150THI
5185/70R13EP150INDO
6175/65R14EP150INDO
7175/70R14EP150THI
8175/70R14MY02THI
9 185/65R14150EZINDO
10185/65R14EP150INDO
11185/65R14TECTHI
12185/65R15T005INDO
13185/65R15T01JAP
14185/65R15TC10INDO
15185/65R15B250JAP
16195/60R15EP150THI
17195/60R15T001JAP
18 195/60R15150EZTHI
19195/65R15MY02THI
20205/70R15694JAP
21225/75R15CT697INDO
22235/95R15CD618JAP
23255/70R15CD840THI
24205/R16CD697THI
25205R16C D840THI
26PSR (HRD)215/45R17T001JAP
27215/50R17EP300THI
28215/55R17GR90INDO
29215/55R17T001JAP
30215/55R17T005JAP
31215/55R17TECTHI
32215/55R17MY02THI
33FS 215/55R17TZ700JAP
34LTR650R16R230JAP
35700R16R230JAP
36750R16R230JAP
37155R12CR624INDO
38155R12CR623INDO
39165R13CR624INDO
40185R14C613VJAP
41185R14CR624INDO
42195R14CR624INDO
43195R14C613VJAP
44195R14CR623THI
45205R14C613VJAP
46205R14CR624INDO
47TBR1200R20 G580 JAP
481200R20 G580THI
491200R20 R187JAP
501200R20 R187THI
511200R20 M802THI
list






EXPECTED RESULT



sort .xls
ABCDEFGHIJKLM
1OctNovDec
2CategorySizePatternOriginArrivedSalesStock Arrived SalesStock Arrived SalesStock
3PSR (LRD)175/70R13B25INDO161845179179
4175/70R13EP150THI---------
5185/70R13EP150INDO---------
6175/65R14EP150INDO---------
7175/70R14EP150THI - - - ------
8175/70R14MY02THI--296-5291--291
9185/65R14TECTHI--- - - - - - -
10185/65R14EP150INDO--- - - - - - -
11 185/65R14150EZINDO
12185/65R15TC10INDO10--
13185/65R15T005INDO--- - - - - - -
14185/65R15T01JAP
15185/65R15B250JAP
16195/60R15EP150THI
17195/60R15T001JAP
18 195/60R15150EZTHI
19195/65R15MY02THI50361361361
20205/70R15694JAP-161616
21225/75R15CT697INDO - - - - - - - - -
22235/95R15CD618JAP - 565656
23255/70R15CD840THI661,3291,3292851,614
24205R16C D840THI
25205/R16CD697THI4293293293
26TTL-1372,535-102,525285-2,810
27PSR (HRD)215/45R17T001JAP---------
28215/50R17EP300THI---------
29215/55R17GR90INDO--- - - - - - -
30215/55R17T001JAP--- - - - - - -
31215/55R17T005JAP
32215/55R17TECTHI--- - - - - - -
33215/55R17MY02THI---------
34FS 215/55R17TZ700JAP---------
35TTL---------
36LTR650R16R23082198211211
37700R16R23012PR31611931351,058177111,224
38750R16R2301,22191051408,9654,46851312,920
39155R12CR624INDO---------
40155R12CR623INDO---------
41165R13CR624INDO---------
42185R14C613VJAP8252525
43185R14CR624INDO-3014297297
44195R14CR624INDO---------
45195R14C613VJAP---------
46195R14CR623THI
47205R14C613VJAP-444
48205R14CR624INDO
49TTL-1,55310,847-28710,5604,64552414,681
50TBR1200R20 G580 JAP3,5702,6411,653988787930845
511200R20 G580THI
521200R20 R187JAP607481418633330
531200R20 R187THI
541200R20 M802THI
55TTL-4,1773,122-2,0711,051787963875
In & Out Balance
Cell Formulas
RangeFormula
M52,J52,M50,J50,M47,J47,M42:M43,J42:J43,M36:M38,M33:M34,J36:J38,J33:J34,M25,J25,M22:M23,J22:J23,M19:M20,J19:J20,M12,J12,M3:M8,J8,J3:J6J3=G3+H3-I3
E26:M26E26=SUM(E3:E25)
G33:G34G33=E33-F33
E35:M35E35=SUM(E27:E34)
F37F37=310+6
E49:M49E49=SUM(E36:E48)
F52F52=586+21
E55:M55E55=SUM(E50:E54)
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
You must log in or register to reply here.

Similar threads

A
  • Solved
transpose data for each sheet based on multiple prices
2
Replies
10
Views
393
abdo meghari
A
A
  • Question Question
populate & update invoice information across sheets based on invoice number
Replies
0
Views
343
abdo meghari
A
A
  • Question Question
replace data based on column between two sheets
Replies
2
Views
403
abdo meghari
A
A
  • Question Question
sort whole ranges based on selection multiple cells at once without any criteria
2 3 4
Replies
32
Views
1K
abdo meghari
A
A
  • Solved
Match three columns with one column and pull numeric values
Replies
4
Views
438
abdo meghari
A

Forum statistics

Threads
1,224,827
Messages
6,181,200
Members
453,022
Latest member
RobertV1609

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