Search and accumulate two rows into new row, then delete the originals

[Snajder]

I have a workbook with 5 columns (actually many more but this makes it easier and smaller to explain) and a number of rows that looks like this:
A B C D E F
1 X 0 0 0
2 V U 2 3 4
3 Y 0 0 0
4 U 5 6 7
5 Z 0 0 0
...

What I am trying to accomplish is a macro (or any other method) to first search the B-column after any value(Isblank?) and when it finds a value in the B column, use this value and search for the same value in the A-column.
Then It should create a new row with the values(text string) from both A-column cells, in this case V + U entered in the C-column and add the values from the D,E,F columns of both the first row where it found the value in the B-column and the second row where it found the value in the A column.
After this is done, both the original rows should be deleted and only this new row should exist.
After this, it should continue and search in the B-column for the next value and repeat this process.

Below is my preferd output:

A B C D E F
1 X 0 0 0
2 Y 0 0 0
3 Z 0 0 0
4 V+U 7 9 11
...


I hope you understand what I am trying to explain
Is this possible?

[HaHoBe]

Hi, Snajder,

as the browser will not display more than one space itīs hard to tell whether I understood correctly what you are after.

This is how I understood your data is laid out:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Ciao,
Holger

[Snajder]

Holger,

Thank you, the principle works but it was harder for me to implement this in my case than I thought. I was trying to do this with several vlookup:s and stuff..

My search value is in the "I" column and the reference value is in the "A" column. If nothing is found in the "I" column, do nothing, if a value is found in the "I" column but no corresponding value is found in the "A" column, mark the "I" cell with red and finally if a value is found both in the A and I columns, create a new row with the original A value in the A-column, all the original values in columns B-H (the values in B-H should be found in the column where the A-value is found) and the I value should be found in the J column. (A is "article number", I is "replaced by article number" and J is "replacing article number")
After this, the values in column M and O should be added together in the new M and O cells of the new row.
Finally the two original rows should be deleted and only the new combined row should be there.

I hope I was clear enough and thank you very much in advance!

Victor

[HaHoBe]

Hi, Victor,

sounds to me that you want to keep me busy rebuilding your worksheet.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Ciao,
Holger

[Snajder]

Thank you Holger,

I think I underestimated my skills so I thought I would be able to ask for a simple example an modify it but..
Your second version seems good, however I seem to sometimes get two errors;
Type mismatch of the row "Set rngFound = Range("A:A").Find(What:=rngCell.Value)"
and
"Cannot perform this on overlapping commands" (or something similiar) on row "rngDelete.EntireRow.Delete"

I'm afraid I can't upload my actual document but I have created a dummy which is attached and the columns I would like to use are in there
I can add that one new Student ID can replace two or more old IDs and sometimes there might be some text in the "new student ID"-cell, due to exports from another application. Is there a way to make sure there are only numbers, no spaces, characters or anything in the cell, before searching?

Thank you once again

[HaHoBe]

Hi, Snajder,

letīs see if we can narrow in on what is wanted:

Please Login or Register  to view this content.

When running the code on your workbook I couldnīt encounter any errors. Maybe you can run a loop like this before trying the substitutions (this assumes that the numbers are at the beginning of the string), cleaning additional spaces could be done by using the Trim-Command or use RegEx/Regular Expressions:

Please Login or Register  to view this content.

Ciao,
Holger

[Snajder]

Now we are getting much closer! If everything in the base data is as i should be, it works fine

Two cases where it doesn't want to cooperate;
I still get the Run-time error "1004": Cannot use that command on overlapping, when executing and when I look at the spreadsheet (the big one),
it could possible be because a student ID that is replacing an older student-ID is then in it's turn being replaced by another even newer student-ID.
It seems like this i creating some kind of evil loop... This system is pretty stupid but it is managed on another level, where I can't influence it..

The second case is if there are one student-id that replaces two older student-ids, then there are two new lines created with the new student-ID, one replacing the first older and one replacing the second older.
In this case it is not possible to add these together since that would mean that the original test values from the new student-id gets added twice which makes it wrong..

The reason for these extra student-ids is that not all students are enrolled in classes as a part of their program but rather enroll as an extra course and in these cases they are assigned a new student-id and sometimes this changes during the program. Stupid? yes quite so...

I have made some very small modification to your code and would like to change the two rows that I have commmented out to a formula that makes N(row)=O(row)/M(row)
Q=R(row)/S(row)
T=U/S
W=X/V
Z=AA/Y
AC=AD/AB

Please Login or Register  to view this content.

And Holger, where do I send you the coupon for a free case of beer?

[HaHoBe]

Hi, Victor,

I had the filldown as your sample hosted the formulas. Maybe give this a try:

Please Login or Register  to view this content.

As long as Iīm working in a forum I just take a virtual one

Ciao,
Holger

[Snajder]

That worked fine!
Have you any suggestions how I can solve the two special cases, when one ID replaces another ID which in turn replaces another ID and the case where on ID replaces two older ones?

Victor