If the ID in column A appears once for the set name in Column C write value in column G

Hi,

This is very difficult to explain but here goes.

If the ID in column A appears once for the set name in Column C then write value in column G
If the ID in column A appears twice for the set name in Column C then write value in column G
If the ID in column A appears three or more times for the set name in column C then write value in column G

The set name changes in Column C so the value in column G will change depending on whether there are 1, 2 or 3+ of the same ID in column A

Column C Column G results
Stop1 2.5 if ID in column A appears once, 3.2 if twice, 4.5 for three times or more
Stop2 2.4 if ID in column A appears once, 3.8 if twice, 4.9 for three times or more
Stop3 2.7 if ID in column A appears once, 3.6 if twice, 4.1 for three times or more
Stop4 2.9 if ID in column A appears once, 3.7 if twice, 4.2 for three times or more
Stop5 3.0 if ID in column A appears once, 3.8 if twice, 4.6 for three times or more
Stop6 1.3 if ID in column A appears once, 1.9 if twice, 2.5 for three times or more
Stop7 3.8 if ID in column A appears once, 4.2 if twice, 4.7 for three times or more
Stop8 3.3 if ID in column A appears once, 3.5 if twice, 4.5 for three times or more


I have attached two files the one names Test.xls is before the VBS is run and Test1.xls shows after the VBS has been run. I'm not trying to create the output in a separate file or sheet, I would just like to update the Test.xls file.

Thanks for any help,

David

take a look at this,

Thanks but I need it in VBS as the data will be imported after values are applied.

I believe by using case = Column C will take care of the specific name, but I'm not sure how to handle counting the occurrances of the ID in column A so that it can be assigned a value based on the case statement.

Any help is appreciated.

David

Could somebody please help?

Thanks

Hi,

Please take a look and advise

Please Login or Register  to view this content.

Thanks,

David

Is there anybody who can help?

Please Login or Register  to view this content.

Thanks Jindon, that works perfectly.

Also I was wondering what would I have to change if I wanted to add more values above 3?

example:

Please Login or Register  to view this content.

Thank you very much for your help,

David

Too vague.

Sorry I would like to be able to have if the ID in column A appears up to 10 times then it would display 7.8 in column G

dic("Stop1") = VBA.Array(2.5, 3.2, 4.5, 4.9, 5.3, 5.8, 6.1, 6.7, 7.3, 7.8)

I hope this makes more sense.

Thanks

Try change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Excellent, thanks for all your help.