adikoldskool
New Member
- Joined
- Feb 2, 2011
- Messages
- 13
SHEET 1 . ( Half of the data)
Tab name: Raw data
*
A**************************B***********************C********** **************** D
NO HEADING 1 HEADING 2 HEADING 3
1 EN 74028JanD 67 HGN
2 EN 74029JanD 99 HGN
3 EN 74030JanD 96 HGN
4 EN 74031JanD 98 HGN
5 EN 74001FebD 58 HGN
6 EN 74002FebD 80 HGN
7 EN 74003FebD 69 HGN
8 EN 74004FebD 85 HGN
9 EN 74005FebD 111 HGN
10 EN 74006FebD 16 HGN
11 EN 74007FebD 19 HGN
12 EN 74008FebD 86 HGN
13 EN 74009FebD 93 HGN
14 EN 74010FebD 109 HGN
15 EN 74011FebD 18 HGN
16 EN 74012FebD 112 HGN
17 EN 74013FebD 90 HGN
18 EN 74014FebD 11 HGN
19 EN 74015FebD 32 HGN
20 EN 74016FebD 77 HGN
21 EN 74017FebD 31 HGN
22 EN 74018FebD 83 HGN
23 EN 74019FebD 108 HGN
24 EN 74020FebD 25 HGN
25 EN 74021FebD 20 HGN
26 EN 74022FebD 25 HGN
27 EN 74023FebD 100 HGN
28 EN 74024FebD 65 HGN
29 EN 74025FebD 40 HGN
30 EN 74026FebD 52 HGN
31 EN 74027FebD 69 HGN
32 EN 74028FebD 90 HGN
33 EN 74001MarD 76 HGN
34 EN 74002MarD 91 HGN
35 EN 74003MarD 109 HGN
36 EN 74128JanA 98 HGN
37 EN 74129JanA 110 HGN
38 EN 74130JanA 65 HGN
39 EN 74131JanA 105 HGN
40 EN 74101FebA 56 HGN
41 EN 74102FebA 76 HGN
42 EN 74103FebA 55 HGN
43 EN 74104FebA 87 HGN
44 EN 74105FebA 88 HGN
45 EN 74106FebA 74 HGN
46 EN 74107FebA 106 HGN
47 EN 74108FebA 40 HGN
48 EN 74109FebA 54 HGN
49 EN 74110FebA 58 HGN
50 EN 74111FebA 15 HGN
51 EN 74112FebA 80 HGN
52 EN 74113FebA 85 HGN
53 EN 74114FebA 59 HGN
54 EN 74115FebA 110 HGN
55 EN 74116FebA 115 HGN
56 EN 74117FebA 108 HGN
57 EN 74118FebA 112 HGN
58 EN 74119FebA 112 HGN
59 EN 74120FebA 104 HGN
60 EN 74121FebA 23 HGN
61 EN 74122FebA 26 HGN
62 EN 74123FebA 32 HGN
63 EN 74124FebA 18 HGN
64 EN 74125FebA 24 HGN
65 EN 74126FebA 103 HGN
66 EN 74127FebA 18 HGN
67 EN 74128FebA 101 HGN
68 EN 74101MarA 62 HGN
69 EN 74102MarA 100 HGN
70 EN 74103MarA 101 HGN
71 EN 74428JanD 75 TAN
72 EN 74429JanD 18 TAN
73 EN 74430JanD 111 TAN
74 EN 74431JanD 115 TAN
75 EN 74401FebD 98 TAN
76 EN 74402FebD 105 TAN
77 EN 74403FebD 18 TAN
78 EN 74404FebD 26 TAN
79 EN 74405FebD 32 TAN
80 EN 74406FebD 31 TAN
81 EN 74407FebD 30 TAN
82 EN 74408FebD 21 TAN
83 EN 74409FebD 30 TAN
84 EN 74410FebD 27 TAN
85 EN 74411FebD 29 TAN
86 EN 74412FebD 107 TAN
87 EN 74413FebD 26 TAN
88 EN 74414FebD 92 TAN
89 EN 74415FebD 60 TAN
90 EN 74416FebD 38 TAN
91 EN 74417FebD 60 TAN
92 EN 74418FebD 22 TAN
93 EN 74419FebD 115 TAN
94 EN 74420FebD 22 TAN
95 EN 74421FebD 115 TAN
96 EN 74422FebD 113 TAN
97 EN 74423FebD 61 TAN
98 EN 74424FebD 59 TAN
99 EN 74425FebD 66 TAN
100 EN 74426FebD 25 TAN
101 YF 85601FebD 93 CMN
102 YF 85601FebD 20 GSN
103 EN 74401MarD 110 TAN
104 EN 74402MarD 100 TAN
*
*
*
Sheet 2
Tab name: Input data (column b2 is empty cells)
*
A******************************** B
HEADING 1 HEADING 3
YF 85601FebD
AH907427FebD ZNG
*
*
Sheet 3
Tab name: Result
(Column D result is based on 1k of data which gives wrong result)
*
A**************************B**********************************C********** ****************
HEADING 1 HEADING 1_1 CONCATENATE. HEADING 3 * * *
YF 85601FebD YF 85601FebD HGN > If "D2"not found in raw data, the formula will lookup the exact match in raw data and return the maximum value based on the entry though theres duplicate entry with different value (e.g raw data, heading 1 contain 2 duplicate entries with different values).
AH 907427FebD AH907427FebD ZNG > The result should be CMN based on the red fonts of the raw data located at the end of the entry
*
*
*
Result tab( Column D2) formula: =IF(ISBLANK('Input data'!B2),INDEX('Raw data'!$B$2:$D$1383,MATCH(MAX(IF('Raw data'!$B$2:$B$1383=Result!C2,'Raw data'!$C$2:$C$1383)),'Raw data'!$C$2:$C$1383,0),3),'Input data'!B2)
*
Result tab( Column D3) formula: =IF(ISBLANK('Input data'!B3),INDEX('Raw data'!$B$2:$D$1383,MATCH(MAX(IF('Raw data'!$B$2:$B$1383=Result!C3,'Raw data'!$C$2:$C$1383)),'Raw data'!$C$2:$C$1383,0),3),'Input data'!B3)
*
*
Quest: The formula gives wrong result when you have more than 1k of data in "Raw data" tab* whereas when theres lesser of data, it gives accurate result. Feel weird.*Is there any other formula that can gives accurate results? thank you very much for the solution.
*
*
Tab name: Raw data
*
A**************************B***********************C********** **************** D
NO HEADING 1 HEADING 2 HEADING 3
1 EN 74028JanD 67 HGN
2 EN 74029JanD 99 HGN
3 EN 74030JanD 96 HGN
4 EN 74031JanD 98 HGN
5 EN 74001FebD 58 HGN
6 EN 74002FebD 80 HGN
7 EN 74003FebD 69 HGN
8 EN 74004FebD 85 HGN
9 EN 74005FebD 111 HGN
10 EN 74006FebD 16 HGN
11 EN 74007FebD 19 HGN
12 EN 74008FebD 86 HGN
13 EN 74009FebD 93 HGN
14 EN 74010FebD 109 HGN
15 EN 74011FebD 18 HGN
16 EN 74012FebD 112 HGN
17 EN 74013FebD 90 HGN
18 EN 74014FebD 11 HGN
19 EN 74015FebD 32 HGN
20 EN 74016FebD 77 HGN
21 EN 74017FebD 31 HGN
22 EN 74018FebD 83 HGN
23 EN 74019FebD 108 HGN
24 EN 74020FebD 25 HGN
25 EN 74021FebD 20 HGN
26 EN 74022FebD 25 HGN
27 EN 74023FebD 100 HGN
28 EN 74024FebD 65 HGN
29 EN 74025FebD 40 HGN
30 EN 74026FebD 52 HGN
31 EN 74027FebD 69 HGN
32 EN 74028FebD 90 HGN
33 EN 74001MarD 76 HGN
34 EN 74002MarD 91 HGN
35 EN 74003MarD 109 HGN
36 EN 74128JanA 98 HGN
37 EN 74129JanA 110 HGN
38 EN 74130JanA 65 HGN
39 EN 74131JanA 105 HGN
40 EN 74101FebA 56 HGN
41 EN 74102FebA 76 HGN
42 EN 74103FebA 55 HGN
43 EN 74104FebA 87 HGN
44 EN 74105FebA 88 HGN
45 EN 74106FebA 74 HGN
46 EN 74107FebA 106 HGN
47 EN 74108FebA 40 HGN
48 EN 74109FebA 54 HGN
49 EN 74110FebA 58 HGN
50 EN 74111FebA 15 HGN
51 EN 74112FebA 80 HGN
52 EN 74113FebA 85 HGN
53 EN 74114FebA 59 HGN
54 EN 74115FebA 110 HGN
55 EN 74116FebA 115 HGN
56 EN 74117FebA 108 HGN
57 EN 74118FebA 112 HGN
58 EN 74119FebA 112 HGN
59 EN 74120FebA 104 HGN
60 EN 74121FebA 23 HGN
61 EN 74122FebA 26 HGN
62 EN 74123FebA 32 HGN
63 EN 74124FebA 18 HGN
64 EN 74125FebA 24 HGN
65 EN 74126FebA 103 HGN
66 EN 74127FebA 18 HGN
67 EN 74128FebA 101 HGN
68 EN 74101MarA 62 HGN
69 EN 74102MarA 100 HGN
70 EN 74103MarA 101 HGN
71 EN 74428JanD 75 TAN
72 EN 74429JanD 18 TAN
73 EN 74430JanD 111 TAN
74 EN 74431JanD 115 TAN
75 EN 74401FebD 98 TAN
76 EN 74402FebD 105 TAN
77 EN 74403FebD 18 TAN
78 EN 74404FebD 26 TAN
79 EN 74405FebD 32 TAN
80 EN 74406FebD 31 TAN
81 EN 74407FebD 30 TAN
82 EN 74408FebD 21 TAN
83 EN 74409FebD 30 TAN
84 EN 74410FebD 27 TAN
85 EN 74411FebD 29 TAN
86 EN 74412FebD 107 TAN
87 EN 74413FebD 26 TAN
88 EN 74414FebD 92 TAN
89 EN 74415FebD 60 TAN
90 EN 74416FebD 38 TAN
91 EN 74417FebD 60 TAN
92 EN 74418FebD 22 TAN
93 EN 74419FebD 115 TAN
94 EN 74420FebD 22 TAN
95 EN 74421FebD 115 TAN
96 EN 74422FebD 113 TAN
97 EN 74423FebD 61 TAN
98 EN 74424FebD 59 TAN
99 EN 74425FebD 66 TAN
100 EN 74426FebD 25 TAN
101 YF 85601FebD 93 CMN
102 YF 85601FebD 20 GSN
103 EN 74401MarD 110 TAN
104 EN 74402MarD 100 TAN
*
*
*
Sheet 2
Tab name: Input data (column b2 is empty cells)
*
A******************************** B
HEADING 1 HEADING 3
YF 85601FebD
AH907427FebD ZNG
*
*
Sheet 3
Tab name: Result
(Column D result is based on 1k of data which gives wrong result)
*
A**************************B**********************************C********** ****************
HEADING 1 HEADING 1_1 CONCATENATE. HEADING 3 * * *
YF 85601FebD YF 85601FebD HGN > If "D2"not found in raw data, the formula will lookup the exact match in raw data and return the maximum value based on the entry though theres duplicate entry with different value (e.g raw data, heading 1 contain 2 duplicate entries with different values).
AH 907427FebD AH907427FebD ZNG > The result should be CMN based on the red fonts of the raw data located at the end of the entry
*
*
*
Result tab( Column D2) formula: =IF(ISBLANK('Input data'!B2),INDEX('Raw data'!$B$2:$D$1383,MATCH(MAX(IF('Raw data'!$B$2:$B$1383=Result!C2,'Raw data'!$C$2:$C$1383)),'Raw data'!$C$2:$C$1383,0),3),'Input data'!B2)
*
Result tab( Column D3) formula: =IF(ISBLANK('Input data'!B3),INDEX('Raw data'!$B$2:$D$1383,MATCH(MAX(IF('Raw data'!$B$2:$B$1383=Result!C3,'Raw data'!$C$2:$C$1383)),'Raw data'!$C$2:$C$1383,0),3),'Input data'!B3)
*
*
Quest: The formula gives wrong result when you have more than 1k of data in "Raw data" tab* whereas when theres lesser of data, it gives accurate result. Feel weird.*Is there any other formula that can gives accurate results? thank you very much for the solution.
*
*
