I've tried searching and I've come close, but not yet close enough to figure this out with my current Excel VBA abilities.
On a weekly basis, we run a job report. It is outputted to Excel from our ERP system. (I'll call this the "new list".) I have developed a second report in Excel which uses some core data from the ERP derived report. (I'll call this the "old list".) My challenge is to update the old list with the info from the new list. It can be done manually, but I'm hoping to (mostly) automate it.
The data is highly structured. I'll explain that below. First, an example of the "old list" and "new list". This represents Col A on 2 different worksheets.
Old List..............New List<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
></o>
<o></o>
CustID5 .............CustID5<o></o>
111........... ........111<o></o>
111-02...............111-02<o></o>
111-05...............111-05<o></o>
111-08...............111-06 ** new entry<o></o>
111-11...............111-08<o></o>
111-20...............111-11<o></o>
111-25...............111-20<o></o>
BB-111...............111-25<o></o>
EE-111................BB-111<o></o>
RR-111................EE-111<o></o>
blank...................RR-111<o></o>
blank...................SS-111 ** new entry<o></o>
CustID2..............blank<o></o>
120....................blank<o></o>
120-01................CustID2<o></o>
120-07................120<o></o>
120-13................120-01<o></o>
AA-120...............120-07<o></o>
RR-120................120-13<o></o>
SS-120................AA-120<o></o>
blank...................RR-120<o></o>
blank...................RR-120-01 **new entry<o></o>
CustID10 ...........SS-120<o></o>
132......................blank<o></o>
132-02.................blank<o></o>
blank...................CustID10<o></o>
blank...................132<o></o>
..........................132-02<o></o>
..........................blank<o></o>
..........................blank<o></o>
..........................CustID6 ** new entry (project)<o></o>
..........................137<o></o>
..........................137-01<o></o>
..........................137-05<o></o>
...........................NN-137<o></o>
Each CustIDx represents a project.
Within each project, there is a list of jobs.
Over time, it is possible for some new jobs to be added (within a project).
Over time, it is possible for new projects to be added.
Projects will eventually be removed from the list (both of them).
I'm ok if I have to do that part manually.
There should never be a case where a job # exists in the old list, but is not in the new list. If by chance this happens, it can be ignored.
The lists are alphanumerically sorted 'except' for the CustIDx which is inserted (in what would otherwise be another blank cell) between the projects. The CustIDx are purely alpha. Job # are alphanumeric. They always have a number component as a minimum.
As I said, these lists are Col A (on 2 different worksheets).
(I can give the worsheets fixed names / # if that helps.)
Col B to G contain data that I want to update.
I want to copy B-G from the new list into the matching entry on the old list.
If a job # exists in the new list but not in the old list, I want to insert it in the old list and again, copy B-G across. Insert means a full row. I have a lot more going on to the right of Col G.
Oh... I also need to copy Col B-G across for the first <blank> row (of every pair). Actually, both rows could be copied... that's fine. The second <blank> is just a blank row.
The old list does not start on a fixed row.
I can give the list(s) a set heading though if that helps.
Whew... Feels like I'm asking a lot. However, I did come across a macro that was doing something similar and was only about a dozen lines long. Unfortunately, it was beyond my ability to modify it.
Thanks for reading. I'm looking forward to any help that I can get.
GTS
On a weekly basis, we run a job report. It is outputted to Excel from our ERP system. (I'll call this the "new list".) I have developed a second report in Excel which uses some core data from the ERP derived report. (I'll call this the "old list".) My challenge is to update the old list with the info from the new list. It can be done manually, but I'm hoping to (mostly) automate it.
The data is highly structured. I'll explain that below. First, an example of the "old list" and "new list". This represents Col A on 2 different worksheets.
Old List..............New List<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o></o><o
></o>CustID5 .............CustID5<o
></o>111........... ........111<o
></o>111-02...............111-02<o
></o>111-05...............111-05<o
></o>111-08...............111-06 ** new entry<o
></o>111-11...............111-08<o
></o>111-20...............111-11<o
></o>111-25...............111-20<o
></o>BB-111...............111-25<o
></o>EE-111................BB-111<o
></o>RR-111................EE-111<o
></o>blank...................RR-111<o
></o>blank...................SS-111 ** new entry<o
></o>CustID2..............blank<o
></o>120....................blank<o
></o>120-01................CustID2<o
></o>120-07................120<o
></o>120-13................120-01<o
></o>AA-120...............120-07<o
></o>RR-120................120-13<o
></o>SS-120................AA-120<o
></o>blank...................RR-120<o
></o>blank...................RR-120-01 **new entry<o
></o>CustID10 ...........SS-120<o
></o>132......................blank<o
></o>132-02.................blank<o
></o>blank...................CustID10<o
></o>blank...................132<o
></o>..........................132-02<o
></o>..........................blank<o
></o>..........................blank<o
></o>..........................CustID6 ** new entry (project)<o
></o>..........................137<o
></o>..........................137-01<o
></o>..........................137-05<o
></o>...........................NN-137<o
></o> Each CustIDx represents a project.
Within each project, there is a list of jobs.
Over time, it is possible for some new jobs to be added (within a project).
Over time, it is possible for new projects to be added.
Projects will eventually be removed from the list (both of them).
I'm ok if I have to do that part manually.
There should never be a case where a job # exists in the old list, but is not in the new list. If by chance this happens, it can be ignored.
The lists are alphanumerically sorted 'except' for the CustIDx which is inserted (in what would otherwise be another blank cell) between the projects. The CustIDx are purely alpha. Job # are alphanumeric. They always have a number component as a minimum.
As I said, these lists are Col A (on 2 different worksheets).
(I can give the worsheets fixed names / # if that helps.)
Col B to G contain data that I want to update.
I want to copy B-G from the new list into the matching entry on the old list.
If a job # exists in the new list but not in the old list, I want to insert it in the old list and again, copy B-G across. Insert means a full row. I have a lot more going on to the right of Col G.
Oh... I also need to copy Col B-G across for the first <blank> row (of every pair). Actually, both rows could be copied... that's fine. The second <blank> is just a blank row.
The old list does not start on a fixed row.
I can give the list(s) a set heading though if that helps.
Whew... Feels like I'm asking a lot. However, I did come across a macro that was doing something similar and was only about a dozen lines long. Unfortunately, it was beyond my ability to modify it.
Thanks for reading. I'm looking forward to any help that I can get.
GTS
Last edited by a moderator:
