Help with macro in excel [Closed]

Report
Posts
1
Registration date
Sunday December 28, 2014
Status
Member
Last seen
December 28, 2014
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
Hi

I need some help in writing a macro for the below:

1. In sheet 1, I have column A with a list of items and many of them will be repetitive, Column B with the current cost for the item. we have column C and D that need to be filled with values from sheet 2
2. Sheet 2. Column A contains the same list of items from column A in sheet 1 but this time it is unique. It is not repetitive. Each items have the cost per month. So column B (header: Jan 2015) has values for month of Jan, Column C (header name Feb 2015) has values for month of Feb and so on.
3. Now I need to look up the value from Cell A2 in sheet 1 with the range column A in sheet 2. Once we look up the value in cell A2 use that row. From that row find the value that is greater that value on Cell B2 in sheet 1 and appears first. For example if value of B2 is 5, we need to find the value greater than 5 in the row we found using first lookup.
4. Once we found the value greater than cell B2 in sheet 1, we need to get that value in Column C in sheet 1
5. The last step is to get the column header of the first greatest value and bring it in column D in sheet 1

I can attach an example sheet if needed

Please help me with a macro to get the values

Thanks

1 reply

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
793
not clear
suppose data is like this

name data
a 1
s 2
d 3
d 4
s 5
a 6
a 7
s 8
d 9


suppose d2 has entry "s" (without quots)
in E2 have this formula

=MAX(IF($A$2:$A$10=D2,$B$2:$B$10))
invoke this formula by
CONTROL SHIFT ENTER

is this what you want????????

you can change D2 as "a" or "d"

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!