Hi all,
I am trying to speed-up my lookup process by employing array. However since I am new in array, I have a lot of difficulties. Normally, I would use application.vlookup to look for a value based on the reference. Now, I am trying to use array..
I have searched for many topic for this, but I have not found the solution.
In my attachment, the file contains four sheets, namely rawdata, base, summary and backlog.
There are two main objectives that I would like to achieve:
1. In sheet rawdata, I would like to fill column Q with Grouping Name which requires me to do double vlookup (when not working with array). First to look for the material group code which can be found from column X sheet rawdata --> then look for the group name based on the material group code and match it with the description as listed in sheet base.
So far, my failed code look like this (I am not even reach the objective of returning material group code)
2. The second question is to get the last usage based on material ID which will be shown in sheet summary:Please Login or Register to view this content.
The step are as follows:
a. First I move the data from column P, E and K sheet rawdata to sheet backlog (column A to C)
b. Sorting the data based on the year, in which the latest year is placed on top
c. Then I number the appearance based on column A, B and C in sheet backlog. At first I am using application.countif but then jindon has given a much faster wayd. Based on the numbering, I would like to show the latest year when the material was used in sheet summary. This was made through code below. The idea of the code is to look for the first appearance of number 1 for matching year, material id and plant and to return the year which is basically the latest year in which the material was used.HTML Code:
My current code, which is long and tends to make the excel crash:
The data shown in file have been truncated except for sheet backlog. The actual data for sheet rawdata column A are more than 60000 rows and the column S are more than 20000 rows. So I am hoping that someone would shed a light to my problem..Please Login or Register to view this content.
Your assistance is greatly appreciated
Bookmarks