Find reoccurring value in a column, copy value in 2nd column, paste value to 3rd column

  • Initially sounds easy. What I really need is a way to rank a set of values in a column that are not ALL the values in a column nor all in an unbroken range. Column N is an example of the ranking for all Column F;F values The included example is all I could come up with to rank only the desired cells for each stop number column. Rank doesn't like a broken range like Rank(F2,F7,F12,1) as it blows up the Rank Syntax with wrongly placed comas or wrong value for the Order parameter. Rank will work even if there are blank cells in the range or even a formula like, IF(O3="","",Rank(...............) is used in a column range resulting in no value.

    What would work is some VBA code to search B:B or even A:A in this example but we'll just use B:B, for first example of a number, copy a value in same row F:F and paste it to same row in column O:O. Continue search in B:B for second example of same number, copy value in same row F:F and paste it to same row in column Q:Q. Continue search in B:B for third example of same number, copy value in same row F:F and paste it to same row in column S:S.

    Do this, shifting the paste cell by 2 from the last until the value in B:B changes then start the routine over with the paste column at O:O.


    The data table, A:A - M:M will always be sorted with A:A values and all the B:B column values will be the same for a variable number of examples until the A:A sort value changes, causing the B:B column value to change to the next value.

    There could be from 3 to 8 examples of the same number in B:B, For each example the copy column always remains F:F but the paste column moves right 2 columns from the last paste.

    When the number in B:B changes the paste column starts again at O:O

    The Cell and Conditional formatting seen in my example can be added after this copy-paste-copy-shift_paste operation is completed, or can be in place as the template while this operation runs.

    I have attached a *.xlsm copy of the refined data table after some processing of an imported *.CSV data has taken place. Also in the *.xlsm is a module that performs the underlining format of each like section of Column A:A that I am trying several modification techniques at this time to solve the problem I am having.

    As always, any help would be greatly appreciated.

  • I have this code that does the first part of the job. It needs a section to check cell from the previous paste part of the code and if it has a value greater than 0 go to "Range"Q" & rCell.Row" for the next paste. Then repeat the check in "Q" and move to "S", until the A:A or rRange value changes, then start the process over on that values data.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!