Log In
Register
Home
September 16, 2016
Offline
I know how to quickly find the next unique cell in a column (shft +cntrl then down arrow + \ ), but I'd like to take it one step further.
Is there a way for me to jump to a cell (in Column A) that is beside the next unique cell in Column B? Does that make sense? The reason I want to jump to it is because I'd like to enter data in Column A, and then copy that data down until the data in Column B changes to something different.
In the attached file, you'll see that in Column A (Batch #) I've entered some Batch #'s. Column B (Fee Type) & Column C (Amount) already have data in every cell. The workbook is sorted by Fee Type. I need to create batches of contributions grouped by fee type. I use a new Batch # for each Fee Type.
My workbook typically has 3000 rows, and about 35 different Fee Types (which means 35 Batch #'s). Some batches have up to 1000 contributions.
Instead of dragging the Batch # down until I see that there's a new Fee Type in the next column, I'd like to be able to double click the corner of the cell with the Batch # in Column A so that it copies down until it recognizes that there's a new (different) Fee Type in Column B.
Does that make sense? And if so, is this possible?
See AnswerActive Member
July 26, 2013
Offline
If you use Shift+Ctrl+down+\ in column B then cursor left into Column A and up 1, Shift+end+up and Ctrl+D that should work (and is actually a lot quicker than it looks!). You could skip the "Up 1" and you would have an entry to edt for the next batch number.
A formula solution might also be worth exploring if your real data is as structured as your example data.
Paul
Answers Post
September 16, 2016
Offline
Wow! Paul, thank you so much! That is so simple, and it works perfectly! I appreciate your help very much!
My real data really is as structured as my example data... well, those three columns are anyway. What type of formula are you thinking could work in this situation?
Again, thank you very much!
VIP
Trusted Members
June 25, 2016
Offline
Hi Sari
Refer attached for a formula solution.
Hope this helps.
Sunny
September 16, 2016
Offline
Wow! You guys are amazing! Thank you so much, Sunny! Your formula did exactly what I am looking for! I must admit, I have no idea what most of the formula means, but I don't care. I copied and pasted it and it is perfection!
Thanks again, Sunny and Paul!
VIP
Trusted Members
June 25, 2016
Offline
Hi Sari
Glad to know the formula is working for you.
Let me try to explain the formula in detail: =IF(B3<>B2,"D"&RIGHT(A2,6)+1,A2)
What the formula does is to check if the Fee Type of cell B3 is different from B2 (B3<>B2)
If it is different then add 1 to the previous Batch # else just repeat the previous Batch #
Here we have a problem if we need to add a 1 as the Batch # is text as it has a prefix D.
So I have to remove the prefix D to get the number BEFORE I can add 1 to it.
The function RIGHT(A2,6) will extract the 6 digit number without the prefix and now I can add 1 to it with RIGHT(A2,6)+1.
Then I need to "join" back the prefix to the new batch # with "D”&RIGHT(A2,6)+1
Hope I explained it clearly enough.
Sunny
1 Guest(s)
