find duplicate and clear possible corresponding matching value

[SarahPintal]

Hi guys,

I cant seem to work out how to reference the drang (named range) and the cell propertly as i keep getting an error.

Everything in the below code works except the parts below in blue

details:
I have in column X lots of names and in column Y (dynamic column\ranges as the users chooses them as per the code) and I want to loop through all names in 'X' and if there are multiple of the same names then for the corresponding 'Y' row entry of each be checked and if they are equal (same $ value) then for one of them of cells in the Y column to be cleared.

Basically if the same name appears more than once it will most likley have the same $ value and if i do a calculation on the $'s it will be 2,3,10 times the amount it should be, hence the need for this .

As per the below code I am trying to use 2 for Each loops though I keep getting an error when trying to reference the drng range which has the values in them..

Please Login or Register  to view this content.

[SarahPintal]

FYI: I would have done something like the below though as the user selects the output range the offset may be 1 column, 10, or 100. I hope that helps paint a better picture.

Please Login or Register  to view this content.

[rylo]

Hi

I think one of your ranges should be drng not both being rng.

That said, can you please attach an example file, and explain what you want to do with that file, and some of the items that would be cleared.

rylo

[SarahPintal]

nw's

I have attached the document.

Basically in the forecast WS there is the WBS column which has a heap of names, and in another column (R) there is the $ value.

I need to scan through the rng's in the WBS column and check if the same name has the same corresponding $ value and if true then clear the contents of of the $ in one of them.

The ranges needs to be referenced rather than a simple offset as the user selects where in the Forecast WS they want the $ to be copied too.

[SarahPintal]

File attached.

[SarahPintal]

Ive added some dummy data .... so that you can follow it through...

Click on the 'Find and calculate actuals' button.. select January, then click on the first WBS entry within the Forecast WS for the input, then click on the 1st black cell in the bleh column (R4)...

it will do the matching and copying, then it will crash, though then you can see that the values copy and you can see where the code crashes (the blue code as per my first post)

[rylo]

Hi

As I suggested, I think you are confusing the ranges. You have 2 variables that are looping through the same range. Is this correct?

How about giving some specifics, noting as you have the specific cells, and give an example of a duplicate, where it is and which part should be cleared. I can't really determine if you want to clear duplicates from the entries in Forecast column J, Forecast column R, or in Integral Dump column A.

rylo

[SarahPintal]

Hi,

The reason I have 2 loops checking the same variable is because that is the only way I could think of checking if the same name appears more than once, and to check if the corresponding $ values for that name match are the same.

I want to clear the contents of the $ value cell in the Forecast WS column 'R' once the find the calculate button has been pressed.

Flow of events:
- User imports Intergral dump WS
- User import Forecast WS
- User clicks 'Find and calculate' button
- User selects month
- User select Input range (the WBS column in the Forecast sheet)
- User selects Output range (in this example the user is going to select Column R in the Forecast WS, but next time may choose another column)

Action:
- WBS names in Input range is matched with the Intergral dump WS
- As the user has selected the month (there is an array built for this) the value is taken from that column and copied to the Ouput range selected.

Problem:
- As there can be multiple of the same WBS names in the input range that will have the same the $ value, the $ value will be added again, and again, and again, thus throwing out the figures.

Fix:
- Only want to keep the $ values once if the there are multiple of the same WBS name in the input range so that the grand total will be accurate.

Maybe there is another way to clear the contents of the cell, there may be a way for them not to be populated in the first place? anyhow, i hope i have clarified it enough, though please let me know.

Thanks

FYI: The dummy data that i just added was created so that once you select Jan for the month, and select the input (J4 of the Forecast Ws) and the output(R4 of the Forecast Ws) the sub will run and you will see that the code has copied multiples

[rylo]

Hi

So if the first appearance of an item in Forecast!J:J was actioned, and all subsequent items had Forecast!R:R cleared, then this would cover this off?

rylo

[SarahPintal]

yep, that is correct.

Only want the items(J:J) $ values to appear once (R:R).

Though please remeber that these ranges (J:J and R:R) are dymanic, so next time it could be A:A & N:N depending on the selected input and output ranges selected.

[rylo]

Hi

Comment out the problem code (watch the positioning of the end with) and replace the block immediately before it with

Please Login or Register  to view this content.

I've added a bit to the line

Please Login or Register  to view this content.

so that it will only action if (a) it finds the item and (b) it is the first appearance of that item, else it will clear out any existing output.

See how that goes.

rylo

[rylo]

Hi

Actually, have a problem with code A.46013630.1.2.06.01.2. There are 3 appearances of it in Integral Dump. One has a blank (first appearance) and you have 2 others with revenue in January.

Exactly what would be the expected result for that code?

rylo

[SarahPintal]

Hi rylo,

Thanks a heap for your efforts.

Dont worry about the issue you pointed out, it will never occur. The only reason it appeared twice in the intergral dump WS is because i put it there manually when i wasnt thinking properly.

I will review your code closer aswell to learn more.

Thanks again