I have a list of account numbers, in order, that might have 2-3 of the same account number in the rows below it based on a column that has a different value in it. I need to copy and paste that different value to the first row that account number appeared, in the next available column in that row, then move to the next new account number and repeat. Appreciate any help.
Thanks
This sounds possible, but it would require a sample workbook in order to really provide any help. Are you able to post a small sample workbook (use: Go Advanced --> Manage Attachments) with all sensitive information altered or removed?
Basically I need to compare column B (acct#) for matching numbers. If there is a match then it needs to validate column a (Ordered Y/N) for a "Y" to move the contents of column d to the first row that account number appears. Attached is what it should look like when finished.
Thanks
If you are open to a formula solution:
1) Select E2,
2) paste the following array entered formula* into the formula bar:Formula:
Please Login or Register to view this content.
3) activate by simultaneously pressing the Ctrl, Shift and Enter keys.
*If you attempt to activate by only pressing the Enter key no value (blank) will display in E2.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Thanks for the formula. The only problem is that if there were three acct #s with y in column a it doesn't fill in the next available column. This is what it should do when there are 3,4,5 etc of the same acct # with y in column a.
Then lets try this two formula solution. The first column, E, is populated by the formula:Subsequent columns are populated by the formula:Formula:Please Login or Register to view this content.Let us know if you have any questions.Formula:Please Login or Register to view this content.
Appreciate your help. The only other concern is when there are three account numbers in a row with Y-N-Y in column A. It will skip the second Y which is the third time the acct number appears. Would it be possible to account for that? I attached a sample of what it looks like when it isn't Y-Y-Y.
Thanks
Feel like I am getting close. The issue is that there are two cases. The case where the 'Last 4' is the same as the 'Account' as in row 2, and the case where the 'Last 4' is different from the 'Account' as in row 10. The formula that handles the case in row 10 is:The array entered formula (see post #4 for activation) that handles the case in row 2, and the remainder of the yellow shaded table is:Formula:Please Login or Register to view this content.I am having a hard time getting the two married up, but will get back to work on this in the morning, provided someone else hasn't solved it by then.Formula:Please Login or Register to view this content.
Let us know if you have any questions.
An array entered formula* (see post #4) that will do what you want is:Let us know if you have any questions.Formula:Please Login or Register to view this content.
Thanks! The issue now is that the formula seems to work well in row 2, but further down in the sample it's pulling in the last 4 of the first acct # in the next blank cell in column E (E:10).
I got myself confused. I thought that E10 should display 5654 since it was different from 1239. However, since that isn't the case it seems that the second (array entered) formula* in post #8 is all that is needed.
- Select cell E2,
- paste the second formula from post #8 into the formula bar,
- *simultaneously press Ctrl,Shift and Enter.
You may then double click the fill handle to copy the formula down, and copy to the right as far as needed.
Let us know if you have any questions.
Works perfectly! Appreciate your help and time!
Thanks
Could this be done in vb instead? I have similar issue.
You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.
I would be interested in the vba as well. The only complaint with the formula is that it is a little laggy with only 1500 rows of data.
VBA for the data in post #3
Please Login or Register to view this content.
The set up of sheet 2 in the attached file may be a less simple, however it may cause less lag in calculation. The proposed solution on sheet 2 uses four helper columns (XFA:XFD). The formula for the first helper column is:The array entered formula* (see post #4) for the remaining columns is:Formula:Please Login or Register to view this content.Note that with 1500 rows of data you will probably need more than 3 columns for the second formula.Formula:Please Login or Register to view this content.
If the problem with lag has to do with the computer stalling when data is being entered into columns A:D then you may want to consider changing the calculation mode for the workbook to manual as mentioned in this article (tip #9) and this article.
Let us know if you have any questions.
This worked great. Thanks
Worked great and saved a lot of time. Appreciate your efforts both JeteMc and Jindon! Just marked as solved.
You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks