Conditional input in a cell based on data in two different cells and other values

[vivekchha]

I have lot of data in three columns like this. The first column is "Name", the second is "Comment" wherein I want the macro to write some comment, the third one is DOB. The problem is that the names in column 1 repeats many times. I want a macro to write in column B "either Old or Older or Oldest" based on the Name and DOB. Thus David with DOB 13 Sep 1982 be marked Oldest in Column B and David with DOB 25 Aug 1988 be marked Older and David with DOB 24 May 1990 be marked Old. Similarly William and Rita should be marked either old or older or oldest. As the data are enormous I do not want to refer the actual name in the programming. I want to call them using a variable in programming.
Name Comment DOB
David 25 August 1988
David 13 September 1982
David 24 May 1990
William 24 March 1980
William 25 July 1987
William 13 August 1989
Rita 17 July 1990
Rita 24 April 1989
Rita 13 June 1988
If such a task can be performed using VBA? Any help.
P.S. The example file is attached

[pike]

hi vivekchha
Will there always be three names?

[davesexcel]

With the assumption that there will be more than three identical items in the first column, try this,

=SUMPRODUCT(($B$4:$B$17=B4)*(D4>$D$4:$D$17))+1&" "&B4, adjust the ranges to suit.

Here is your example back

[vivekchha]

hi vivekchha
Will there always be three names?

Thanks Pike,
Some time there may be two names and some times there may be four or five names also.

[vivekchha]

With the assumption that there will be more than three identical items in the first column, try this,

=SUMPRODUCT(($B$4:$B$17=B4)*(D4>$D$4:$D$17))+1&" "&B4, adjust the ranges to suit.

Here is your example back

Thanks Moderator,
You have done it but my problem is that I do not want to rank them. I want to put some other comments based on the ranking. That's why I took the example of Old, older or oldest. Any way, I liked the way you have used the SUMPRODUCT function. Will you please throw some light on how the logic is flowing in the above formula.

[davesexcel]

Thanks Moderator,
You have done it but my problem is that I do not want to rank them. I want to put some other comments based on the ranking. That's why I took the example of Old, older or oldest. Any way, I liked the way you have used the SUMPRODUCT function. Will you please throw some light on how the logic is flowing in the above formula.

I suppose you could use a nested if if there are not a lot of criterias.
=if(sumproduct(blah,blah)=1,"Old", if(sumproduct(blah,blah)=2,"Older"

Not tested.

Or you may have to use a vlookup funtion.

[DonkeyOte]

Will you please throw some light on how the logic is flowing in the above formula.

For info. on SUMPRODUCT and how it works see the link in my sig. to Bob Phillips' white paper.

I do not want to rank them. I want to put some other comments based on the ranking.

You should as outlined use a LOOKUP based approach on the SUMPRODUCT output

Please Login or Register  to view this content.

You would not want to embed the same SUMPRODUCT function repeatedly within in an IF

[DonkeyOte]

I should add - sorting source data nearly always allows you to use more efficient approaches.

If you were to sort your data by Name (ascending) and then DOB (descending) then you could simply use:

Please Login or Register  to view this content.

(adjusting COUNTIF range as req. - ie to encapsulate your data)

[vivekchha]

I should add - sorting source data nearly always allows you to use more efficient approaches.

If you were to sort your data by Name (ascending) and then DOB (descending) then you could simply use:

Please Login or Register  to view this content.

(adjusting COUNTIF range as req. - ie to encapsulate your data)

Thanks brother,
Thanks brother,
Your method is working fine. But the method is not working if the name occurs only once in column B or it occurs twice in column B. I would like to mark only "Old", if a name occur only one time and I would like to mark only "Old" and "Older" if the name occur two times and I would like to mark "Old", "Older","Oldest" and "Oldest2" if the name occur 4 times. Similarly "Oldest3" for the fifth time and "Oldest4" for the sixth time. Six is the maximum time a name can repeat in column B.
At prsent the method suggested by you mark "Oldest" if a name occurs only one time and marks "Older" and "Oldest" if the name occur two times.
I have got a lot of insight from your suggestion. P.S. Thanks for the recommended readings.
Thanks again.

[DonkeyOte]

if the name occurs only once in column B or it occurs twice in column B. I would like to mark only "Old", if a name occur only one time and I would like to mark only "Old" and "Older" if the name occur two times and I would like to mark "Old", "Older","Oldest" and "Oldest2" if the name occur 4 times. Similarly "Oldest3" for the fifth time and "Oldest4" for the sixth time. Six is the maximum time a name can repeat in column B.

Same principle regards sorted data but rule reversed and range modified to look only at prior and current record(s):

Please Login or Register  to view this content.

[vivekchha]

Thanks brother,
Your method is working very nicely. Will you please explain me the logic in this method. I understand the lookup function and also countif but I do not understand the arrays.
Thanks again.

[DonkeyOte]

The inline arrays { } are an alternative to specifying ranges - useful if the values within are constants (ie do not change).

If you opted to store the outputs Old, Older, Oldest, Oldest2, Oldest3, Oldest4 in say a range for ex. E1:E6 you could use:

=INDEX($E$1:$E$6,COUNTIF($B$4:$B4,$B4))

and copy down

For more info. on inline array constants and their use see:

http://office.microsoft.com/en-us/ex...872911033.aspx
http://www.bettersolutions.com/excel...N516980331.htm
etc...

(ie google and you will find!)

[vivekchha]

Thanks brother,
Any suggestion as to how I can use this method in VBA.

[DonkeyOte]

You will need to elaborate.

Please do not quote entire posts in your response - use the Post Reply button or if you opt to Reply via Quote please Quote only those parts required to make sense of your subsequent post. Unnec. quotes merely clutter up the board.

[vivekchha]

Big Brother,
The following method is working fine in excel after sorting the data in ascending order first using the DOB key and again sorting the data in ascending order using the Name key.
C4:
=LOOKUP(COUNTIF(B4:B14,B4),{1,2,3,4,5,6},{"Old","Older","Oldest","Oldest2","Oldest3","Oldest4"})
I have tried to use it in VBA using the following codes:
Workbooks("Name").Worksheets("Data").Range("C4").FormulaArray= "=LOOKUP(COUNTIF(B4:B14,B4),{1,2,3,4,5,6},{"Old","Older","Oldest","Oldest2","Oldest3","Oldest4"})"

But the codes are reddened and does not work. Will you please suggest where is the mistake. I have also tried using RC type of cell references but that too is not working.

[AGA]

I have lots of data in 4 columns. I need to copy to the next column 'E' the latest 'Total holding' for each year for each director's id, if the value is not the latest from a particular year I need '0'.
So I need E2=12,509.00
E3=0
E4=0
E5=88,282.00
E6=25,282.00
and so on...
Any help, please??

[davesexcel]

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.