# A Complex Combination of Indexing, Matching and Formatting Huge Data

#### etmzkn

##### New Member
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
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
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
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
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
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)

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Replies
8
Views
153
Replies
1
Views
68
Replies
5
Views
285
Replies
3
Views
72
Replies
10
Views
249

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,876
Messages
5,834,194
Members
430,263
Latest member
abz54

### 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.

### Which adblocker are you using?

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

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