A Complex Combination of Indexing, Matching and Formatting Huge Data

etmzkn

New Member
Joined
Dec 19, 2014
Messages
9
Hi again.

I have a problem with a data and its allocation. I will try to explain every single step as I can. I have created explanations for the letters on the excel sheets. Please follow the letters in order to understand the whole content. Thank you in advance.

Informations:

a. Colored cells indicates input cells. Say for this example it is customer "A" and current year "2015". These fields may change.
b. There are customers, inventories and suppliers.
c. Inventories, as seen on the adjacent list, are "x","y","z" and "t".
d. And there are lots of suppliers. Each inventory can be provided by different supplier.
e. Some of the suppliers may provide same inventories. Also some of the inventories are sold by different suppliers.
f. Each customer would buy either some or all type of inventories.

Macro:

g. I would like to execute a macro which will search the "Data" sheet for the related customer (in this example for customer "A").
h. After finding the customer, next step is to search for the related inventory.
i. Say if customer "A" buys "y", "z" and "t" macro will search the related items on the data sheet considering the "current and previous years" and will copy and paste them to this sheet.
j. Pink cells indicates the copied and pasted values.

Results:

k. Since there is no relevant figure on the data sheet about inventory "x" for customer "A", just copied total figure of inventory "x" and paste it here. And deleted un-used rows.
l. Since there are 4 suppliers of inventory "y" on the data sheet for customer "A", macro should insert sufficent rows to inventory "y"s field and paste them as value. The total row of inventory "y" should has a total of pasted values.
m. Same as inventory "y" but inventory "z" has only 2 suppliers, so 2 rows should be inserted by macro.
n. Same as inventory "y" but inventory "t" has 4 suppliers, so 4 rows should be inserted by macro.
o. Since there is no relevant figure on the data sheet about inventory "x" for customer "E", just copied total figure of inventory "x" and paste it here. And deleted un-used rows.
p. Since there are 4 suppliers of inventory "y" on the data sheet for customer "E", macro should insert sufficent rows to inventory "y"s field and paste them as value. The total row of inventory "y" should has a total of pasted values.
q. Same as inventory "y" but inventory "z" has only 2 suppliers, so 2 rows should be inserted by macro.



Excel 2013
Row\Col
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
AA
AB
AC
AD
AE
AF
AG
AH
AI
AJ
AK
AL
AM
5
6
Flag
1​
1​
1​
1​
1​
1​
1​
1​
1​
1​
1​
1​
2​
2​
2​
2​
2​
2​
2​
2​
2​
2​
2​
2​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
7
8
Year
2014
2014
2014
2014
2014
2014
2014
2014
2014
2014
2014
2014
2015
2015
2015
2015
2015
2015
2015
2015
2015
2015
2015
2015
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
9
10
A
198
208
218
228
238
248
258
268
278
288
298
308
166
176
186
196
206
216
226
236
246
256
266
276
166
176
186
196
206
216
226
236
246
256
266
276
11
x
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
12
y
108
112
116
120
124
128
132
136
140
144
148
152
76
80
84
88
92
96
100
104
108
112
116
120
76
80
84
88
92
96
100
104
108
112
116
120
13
aa
9​
10​
11​
12​
13​
14​
15​
16​
17​
18​
19​
20​
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​
14
ab
21​
22​
23​
24​
25​
26​
27​
28​
29​
30​
31​
32​
13​
14​
15​
16​
17​
18​
19​
20​
21​
22​
23​
24​
13​
14​
15​
16​
17​
18​
19​
20​
21​
22​
23​
24​
15
ac
33​
34​
35​
36​
37​
38​
39​
40​
41​
42​
43​
44​
25​
26​
27​
28​
29​
30​
31​
32​
33​
34​
35​
36​
25​
26​
27​
28​
29​
30​
31​
32​
33​
34​
35​
36​
16
ad
45​
46​
47​
48​
49​
50​
51​
52​
53​
54​
55​
56​
37​
38​
39​
40​
41​
42​
43​
44​
45​
46​
47​
48​
37​
38​
39​
40​
41​
42​
43​
44​
45​
46​
47​
48​
17
z
14
16
18
20
22
24
26
28
30
32
34
36
14
16
18
20
22
24
26
28
30
32
34
36
14
16
18
20
22
24
26
28
30
32
34
36
18
ba
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​
19
bb
13​
14​
15​
16​
17​
18​
19​
20​
21​
22​
23​
24​
13​
14​
15​
16​
17​
18​
19​
20​
21​
22​
23​
24​
13​
14​
15​
16​
17​
18​
19​
20​
21​
22​
23​
24​
20
t
76
80
84
88
92
96
100
104
108
112
116
120
76
80
84
88
92
96
100
104
108
112
116
120
76
80
84
88
92
96
100
104
108
112
116
120
21
ca
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​
22
cb
13​
14​
15​
16​
17​
18​
19​
20​
21​
22​
23​
24​
13​
14​
15​
16​
17​
18​
19​
20​
21​
22​
23​
24​
13​
14​
15​
16​
17​
18​
19​
20​
21​
22​
23​
24​
23
cc
25​
26​
27​
28​
29​
30​
31​
32​
33​
34​
35​
36​
25​
26​
27​
28​
29​
30​
31​
32​
33​
34​
35​
36​
25​
26​
27​
28​
29​
30​
31​
32​
33​
34​
35​
36​
24
cd
37​
38​
39​
40​
41​
42​
43​
44​
45​
46​
47​
48​
37​
38​
39​
40​
41​
42​
43​
44​
45​
46​
47​
48​
37​
38​
39​
40​
41​
42​
43​
44​
45​
46​
47​
48​
25
B
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
26
C
39
42
45
48
51
54
57
60
63
66
69
72
39
42
45
48
51
54
57
60
63
66
69
72
39
42
45
48
51
54
57
60
63
66
69
72
27
z
39
42
45
48
51
54
57
60
63
66
69
72
39
42
45
48
51
54
57
60
63
66
69
72
39
42
45
48
51
54
57
60
63
66
69
72
28
da
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​
29
db
13​
14​
15​
16​
17​
18​
19​
20​
21​
22​
23​
24​
13​
14​
15​
16​
17​
18​
19​
20​
21​
22​
23​
24​
13​
14​
15​
16​
17​
18​
19​
20​
21​
22​
23​
24​
30
dc
25​
26​
27​
28​
29​
30​
31​
32​
33​
34​
35​
36​
25​
26​
27​
28​
29​
30​
31​
32​
33​
34​
35​
36​
25​
26​
27​
28​
29​
30​
31​
32​
33​
34​
35​
36​
31
D
166
176
186
196
206
216
226
236
246
256
266
276
166
176
186
196
206
216
226
236
246
256
266
276
166
176
186
196
206
216
226
236
246
256
266
276
32
y
76
80
84
88
92
96
100
104
108
112
116
120
76
80
84
88
92
96
100
104
108
112
116
120
76
80
84
88
92
96
100
104
108
112
116
120
33
aa
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​
34
ab
13​
14​
15​
16​
17​
18​
19​
20​
21​
22​
23​
24​
13​
14​
15​
16​
17​
18​
19​
20​
21​
22​
23​
24​
13​
14​
15​
16​
17​
18​
19​
20​
21​
22​
23​
24​
35
ac
25​
26​
27​
28​
29​
30​
31​
32​
33​
34​
35​
36​
25​
26​
27​
28​
29​
30​
31​
32​
33​
34​
35​
36​
25​
26​
27​
28​
29​
30​
31​
32​
33​
34​
35​
36​
36
ad
37​
38​
39​
40​
41​
42​
43​
44​
45​
46​
47​
48​
37​
38​
39​
40​
41​
42​
43​
44​
45​
46​
47​
48​
37​
38​
39​
40​
41​
42​
43​
44​
45​
46​
47​
48​
37
z
14
16
18
20
22
24
26
28
30
32
34
36
14
16
18
20
22
24
26
28
30
32
34
36
14
16
18
20
22
24
26
28
30
32
34
36
38
ba
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​
39
bb
13​
14​
15​
16​
17​
18​
19​
20​
21​
22​
23​
24​
13​
14​
15​
16​
17​
18​
19​
20​
21​
22​
23​
24​
13​
14​
15​
16​
17​
18​
19​
20​
21​
22​
23​
24​
40
t
76
80
84
88
92
96
100
104
108
112
116
120
76
80
84
88
92
96
100
104
108
112
116
120
76
80
84
88
92
96
100
104
108
112
116
120
41
ca
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​
42
cb
13​
14​
15​
16​
17​
18​
19​
20​
21​
22​
23​
24​
13​
14​
15​
16​
17​
18​
19​
20​
21​
22​
23​
24​
13​
14​
15​
16​
17​
18​
19​
20​
21​
22​
23​
24​
43
cc
25​
26​
27​
28​
29​
30​
31​
32​
33​
34​
35​
36​
25​
26​
27​
28​
29​
30​
31​
32​
33​
34​
35​
36​
25​
26​
27​
28​
29​
30​
31​
32​
33​
34​
35​
36​
44
cd
37​
38​
39​
40​
41​
42​
43​
44​
45​
46​
47​
48​
37​
38​
39​
40​
41​
42​
43​
44​
45​
46​
47​
48​
37​
38​
39​
40​
41​
42​
43​
44​
45​
46​
47​
48​
45
E
96
102
108
114
120
126
132
138
144
150
156
162
90
96
102
108
114
120
126
132
138
144
150
156
90
96
102
108
114
120
126
132
138
144
150
156
46
x
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
47
y
80
84
88
92
96
100
104
108
112
116
120
124
76
80
84
88
92
96
100
104
108
112
116
120
76
80
84
88
92
96
100
104
108
112
116
120
48
aa
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​
13​
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​
49
ab
14​
15​
16​
17​
18​
19​
20​
21​
22​
23​
24​
25​
13​
14​
15​
16​
17​
18​
19​
20​
21​
22​
23​
24​
13​
14​
15​
16​
17​
18​
19​
20​
21​
22​
23​
24​
50
ac
26​
27​
28​
29​
30​
31​
32​
33​
34​
35​
36​
37​
25​
26​
27​
28​
29​
30​
31​
32​
33​
34​
35​
36​
25​
26​
27​
28​
29​
30​
31​
32​
33​
34​
35​
36​
51
ad
38​
39​
40​
41​
42​
43​
44​
45​
46​
47​
48​
49​
37​
38​
39​
40​
41​
42​
43​
44​
45​
46​
47​
48​
37​
38​
39​
40​
41​
42​
43​
44​
45​
46​
47​
48​
52
z
16
18
20
22
24
26
28
30
32
34
36
38
14
16
18
20
22
24
26
28
30
32
34
36
14
16
18
20
22
24
26
28
30
32
34
36
53
ba
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​
13​
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​
54
bb
14​
15​
16​
17​
18​
19​
20​
21​
22​
23​
24​
25​
13​
14​
15​
16​
17​
18​
19​
20​
21​
22​
23​
24​
13​
14​
15​
16​
17​
18​
19​
20​
21​
22​
23​
24​
55
Sheet: Data (2)


Excel 2013
Row\Col
A
B
C
D
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
AA
AB
AC
AD
AE
AF
AG
AH
AI
AJ
AK
AL
AM
AN
AO
AP
AQ
1
2
Before macro
3
Customer
A
4
Current year
2015
5
Previous year
2014​
6
7
Inventories
2014
2014
2014
2014
2014
2014
2014
2014
2014
2014
2014
2014
2015
2015
2015
2015
2015
2015
2015
2015
2015
2015
2015
2015
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
8
x
9
y
10
z
11
t
12
13
After macro
14
15
Customer
A
16
Current year
2015
17
Previous year
2014​
18
19
Inventories
2014
2014
2014
2014
2014
2014
2014
2014
2014
2014
2014
2014
2015
2015
2015
2015
2015
2015
2015
2015
2015
2015
2015
2015
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
20
x ( l.)
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
21
y ( m.)
108​
112​
116​
120​
124​
128​
132​
136​
140​
144​
148​
152​
76​
80​
84​
88​
92​
96​
100​
104​
108​
112​
116​
120​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
22
9
10
11
12
13
14
15
16
17
18
19
20
1
2
3
4
5
6
7
8
9
10
11
12
23
21
22
23
24
25
26
27
28
29
30
31
32
13
14
15
16
17
18
19
20
21
22
23
24
24
33
34
35
36
37
38
39
40
41
42
43
44
25
26
27
28
29
30
31
32
33
34
35
36
25
45
46
47
48
49
50
51
52
53
54
55
56
37
38
39
40
41
42
43
44
45
46
47
48
26
z ( n.)
14​
16​
18​
20​
22​
24​
26​
28​
30​
32​
34​
36​
14​
16​
18​
20​
22​
24​
26​
28​
30​
32​
34​
36​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
27
1
2
3
4
5
6
7
8
9
10
11
12
1
2
3
4
5
6
7
8
9
10
11
12
28
13
14
15
16
17
18
19
20
21
22
23
24
13
14
15
16
17
18
19
20
21
22
23
24
29
t ( o.)
76​
80​
84​
88​
92​
96​
100​
104​
108​
112​
116​
120​
76​
80​
84​
88​
92​
96​
100​
104​
108​
112​
116​
120​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
30
1
2
3
4
5
6
7
8
9
10
11
12
1
2
3
4
5
6
7
8
9
10
11
12
31
13
14
15
16
17
18
19
20
21
22
23
24
13
14
15
16
17
18
19
20
21
22
23
24
32
25
26
27
28
29
30
31
32
33
34
35
36
25
26
27
28
29
30
31
32
33
34
35
36
33
37
38
39
40
41
42
43
44
45
46
47
48
37
38
39
40
41
42
43
44
45
46
47
48
34
35
Another example
36
After macro
37
38
Customer
E
39
Current year
2015
40
Previous year
2014​
41
42
Inventories
2014
2014
2014
2014
2014
2014
2014
2014
2014
2014
2014
2014
2015
2015
2015
2015
2015
2015
2015
2015
2015
2015
2015
2015
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
43
x ( p.)
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
44
y ( q.)
80​
84​
88​
92​
96​
100​
104​
108​
112​
116​
120​
124​
76​
80​
84​
88​
92​
96​
100​
104​
108​
112​
116​
120​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
45
2
3
4
5
6
7
8
9
10
11
12
13
1
2
3
4
5
6
7
8
9
10
11
12
46
14
15
16
17
18
19
20
21
22
23
24
25
13
14
15
16
17
18
19
20
21
22
23
24
47
26
27
28
29
30
31
32
33
34
35
36
37
25
26
27
28
29
30
31
32
33
34
35
36
48
38
39
40
41
42
43
44
45
46
47
48
49
37
38
39
40
41
42
43
44
45
46
47
48
49
z ( r.)
16​
18​
20​
22​
24​
26​
28​
30​
32​
34​
36​
38​
14​
16​
18​
20​
22​
24​
26​
28​
30​
32​
34​
36​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
50
2
3
4
5
6
7
8
9
10
11
12
13
1
2
3
4
5
6
7
8
9
10
11
12
51
14
15
16
17
18
19
20
21
22
23
24
25
13
14
15
16
17
18
19
20
21
22
23
24
52
Sheet: Test (2)
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Watch MrExcel Video

Forum statistics

Threads
1,122,689
Messages
5,597,559
Members
414,156
Latest member
WDMix

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
Top