Combining 2 lines into 1

[ShcreeminEagle]

I would like to combine the information in rows 10 and 11. At the end of January this year my company changed how we name our customers internally; initially there was a number, that has since been removed. I want columns B-U to have the information from row 11 and columns V-AA to have the information from row 10 and row 11 (there will only be a number in one row, never both). I tried making another spreadsheet to reference this one with the following function:

=IF(AND([NB.xlsx]NB!$B10<>[NB.xlsx]NB!$B11,[NB.xlsx]NB!$E10=[NB.xlsx]NB!$E11),[NB.xlsx]NB!V10+[NB.xlsx]NB!V11,[NB.xlsx]NB!V10)

This almost does everything I need. Row 11 has all the information I need. My issue is that row 10 needs to be deleted. Does anyone know how to do this with a macro or VBA? I have over 400 instances this needs to happen otherwise I would do this manually.

Combine 2 lines into 1.png

[alansidman]

Hi and welcome to the forum

Pictures are of little value. Honestly, no one wants to re-type your data to try and solve your issue. Additionally, we would only be guessing at how your data was structured, ie. formulas, formatting, etc. Additionally, due to how some browsers behave, many of our members cannot see uploaded pictures/images. Please do not take this route.

Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" of what you wish the output to be.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[ShcreeminEagle]

Alan,

Thank you for your reply, I wanted to add a spreadsheet when I initially posted but I did not immediately see how to

[JeteMc]

This proposed solution is three-stepped and formula based.
The first step (highlighted orange) is to populate the cells in column A so that they identify those rows that should be kept using: =E7<>E6
The second step (highlighted green) sums the numbers in the "Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec" columns that have the same part #'s in column E using:

Please Login or Register  to view this content.

The second step (highlighted blue) keeps only the specified rows:

Please Login or Register  to view this content.

Let us know if you have any questions.

[ShcreeminEagle]

This works well, thank you!

Would you please explain the "aggregate" function syntax though? I understand the 15 and 6 but the following confuses me

(ROW(A$22:A$30)-ROW(A$21))/(($A$22:$A$30="Keep")+($A$22:$A$30=TRUE)),ROWS(A$34:A34)))

I presume the '-', '/' and '+' are not the mathematical symbols for subtraction, division and addition but something else entirely, I'd like to understand what they mean in this syntax so I can apply them in the future.

Thanks again

[JeteMc]

Using the Evaluate Formula feature on the Formulas tab it can be seen that the -,+ and / pretty much work mathematically.
ROW(A$22:A$30) produces an array 22, 23,... 30
-ROW(A$21) subtracts 21 from each member of the array producing 1, 2,... 9
Both ($A$22:$A$30="Keep") and ($A$22:$A$30=TRUE) produce arrays on TRUE/FALSE and the + operator will do boolean addition (true + true = 1, true + false = 1, false + false = 0).
The / operator then divides the row numbers by either 1 or 0. When divided by 0 it produces a #DIV/0! error which is handled by the 6 option.
I hope that this helps.
Let us know if you have any questions.