Please allow me to create a new thread, I think I overcomplicated the original.
I have a spreadsheet with 4 sheets within it. 1-Coversheet,2-Original BoM,3-New BoM, 4-Dashboard
The user will enter some information on the Coversheet, then copy and paste some table information into both the original BoM and New BoM tabs. This ends the user inputs, other than perhaps clicking a box to run the following.
I have no idea how to write VBA, but I'm pretty sure it required to perform what I need.
I need it to,
1. Look at the sheet called 'Original BoM' and the table held there (needs to be a table as this tool will work for different systems of different sizes).
2. Taking data from this table, it should write into a new table held in a sheet labelled 'Dashboard'. This table starts in C17 to G17 (headers C16-G17),
[TABLE="width: 500"]
<tbody>[TR]
[TD]Original ID[/TD]
[TD]Master ID List[/TD]
[TD]Up to Date ID[/TD]
[TD]Nomenclature[/TD]
[TD]Part Description[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
taking the data from the 'original BoM' sheet, it should
a)concatenate 3 things, (a value held in a sheet called 'Coversheet' in cell $C$2) &(the value in the A column) & (the value held in the B column), and write this in the Dashboard Original ID (column C) & in the Master ID (Column D) List.
b)place the value in the c column in the Dashboard F column.
c)place the value in the d column in the Dashboard G column.
d)repeat a-c down the table in the original BoM sheet until there stops being data (the entire row of data will be valid providing there is a number held in the B Column, if there is text it is not valid).
3.
Repeat step 2, but for the 'New BoM tab', this data is written in the same table as step 2, perhaps underneath it? But note that for step a) the concatenated values will be written into the Master ID (Column D) and Up to Date ID (Column E).
4. The table will most likely now contain duplicate rows, i.e two instances of the same Master ID (column D). One instance of these duplicates needs to be removed, and re-write the remaining row to show the ID is valid for both the original and up to date. i.e
[TABLE="width: 500"]
<tbody>[TR]
[TD]Original ID[/TD]
[TD]Master ID List[/TD]
[TD]Up to Date ID[/TD]
[TD]Nomenclature[/TD]
[TD]Part Description[/TD]
[/TR]
[TR]
[TD][TABLE="width: 219"]
<tbody>[TR]
[TD]511VSB2200770000356[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 219"]
<tbody>[TR]
[TD]511VSB2200770000356[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][/TD]
[TD]Place[/TD]
[TD]thing[/TD]
[/TR]
[TR]
[TD]511VSB2200770000111[/TD]
[TD]511VSB2200770000111[/TD]
[TD][/TD]
[TD]otherplace[/TD]
[TD]otherthing[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]511VSB2200770000356[/TD]
[TD]511VSB2200770000356[/TD]
[TD]Place[/TD]
[TD]thing[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
becomes
[TABLE="width: 500"]
<tbody>[TR]
[TD]Original ID[/TD]
[TD]Master ID List[/TD]
[TD]Up to Date ID[/TD]
[TD]Nomenclature[/TD]
[TD]Part Description[/TD]
[/TR]
[TR]
[TD]511VSB2200770000111[/TD]
[TD]511VSB2200770000111[/TD]
[TD][/TD]
[TD]otherplace[/TD]
[TD]otherthing[/TD]
[/TR]
[TR]
[TD]511VSB2200770000356[/TD]
[TD]511VSB2200770000356[/TD]
[TD]511VSB2200770000356[/TD]
[TD]Place[/TD]
[TD]thing[/TD]
[/TR]
</tbody>[/TABLE]
5. The table should now have no duplicate instances of the Master ID now, but the final step should be to resort the table based on the last 8 digits of the Master ID list, finally looking something like this.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Original ID[/TD]
[TD]Master ID List[/TD]
[TD]Up to Date ID[/TD]
[TD]Nomenclature[/TD]
[TD]Part Description[/TD]
[/TR]
[TR]
[TD]123ABC1234500000001[/TD]
[TD]123ABC1234500000001[/TD]
[TD]123ABC1234500000001[/TD]
[TD]place[/TD]
[TD]screw[/TD]
[/TR]
[TR]
[TD]123ABC1234500000003[/TD]
[TD]123ABC1234500000003[/TD]
[TD]123ABC1234500000003[/TD]
[TD]otherplace[/TD]
[TD]screw[/TD]
[/TR]
[TR]
[TD]123ABC1234500000004[/TD]
[TD]123ABC1234500000004[/TD]
[TD][/TD]
[TD]australia[/TD]
[TD]bolt[/TD]
[/TR]
[TR]
[TD]123ABC1234500000009[/TD]
[TD]123ABC1234500000009[/TD]
[TD][/TD]
[TD]themoon[/TD]
[TD]thing[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]123ABC1234500000020[/TD]
[TD]123ABC1234500000020[/TD]
[TD]thatplace[/TD]
[TD]thing[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]123ABC1234500000100[/TD]
[TD]123ABC1234500000100[/TD]
[TD]neverland[/TD]
[TD]thing[/TD]
[/TR]
[TR]
[TD]123ABC1234500000999[/TD]
[TD]123ABC1234500000999[/TD]
[TD]123ABC1234500000999[/TD]
[TD]nowhere[/TD]
[TD]plank[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]123ABC1234500123456[/TD]
[TD]123ABC1234500123456[/TD]
[TD]themoon[/TD]
[TD]goose[/TD]
[/TR]
[TR]
[TD]123ABC1234500123457[/TD]
[TD]123ABC1234500123457[/TD]
[TD][/TD]
[TD]somewhere[/TD]
[TD]duck[/TD]
[/TR]
</tbody>[/TABLE]
I have a spreadsheet with 4 sheets within it. 1-Coversheet,2-Original BoM,3-New BoM, 4-Dashboard
The user will enter some information on the Coversheet, then copy and paste some table information into both the original BoM and New BoM tabs. This ends the user inputs, other than perhaps clicking a box to run the following.
I have no idea how to write VBA, but I'm pretty sure it required to perform what I need.
I need it to,
1. Look at the sheet called 'Original BoM' and the table held there (needs to be a table as this tool will work for different systems of different sizes).
2. Taking data from this table, it should write into a new table held in a sheet labelled 'Dashboard'. This table starts in C17 to G17 (headers C16-G17),
[TABLE="width: 500"]
<tbody>[TR]
[TD]Original ID[/TD]
[TD]Master ID List[/TD]
[TD]Up to Date ID[/TD]
[TD]Nomenclature[/TD]
[TD]Part Description[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
taking the data from the 'original BoM' sheet, it should
a)concatenate 3 things, (a value held in a sheet called 'Coversheet' in cell $C$2) &(the value in the A column) & (the value held in the B column), and write this in the Dashboard Original ID (column C) & in the Master ID (Column D) List.
b)place the value in the c column in the Dashboard F column.
c)place the value in the d column in the Dashboard G column.
d)repeat a-c down the table in the original BoM sheet until there stops being data (the entire row of data will be valid providing there is a number held in the B Column, if there is text it is not valid).
3.
Repeat step 2, but for the 'New BoM tab', this data is written in the same table as step 2, perhaps underneath it? But note that for step a) the concatenated values will be written into the Master ID (Column D) and Up to Date ID (Column E).
4. The table will most likely now contain duplicate rows, i.e two instances of the same Master ID (column D). One instance of these duplicates needs to be removed, and re-write the remaining row to show the ID is valid for both the original and up to date. i.e
[TABLE="width: 500"]
<tbody>[TR]
[TD]Original ID[/TD]
[TD]Master ID List[/TD]
[TD]Up to Date ID[/TD]
[TD]Nomenclature[/TD]
[TD]Part Description[/TD]
[/TR]
[TR]
[TD][TABLE="width: 219"]
<tbody>[TR]
[TD]511VSB2200770000356[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 219"]
<tbody>[TR]
[TD]511VSB2200770000356[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][/TD]
[TD]Place[/TD]
[TD]thing[/TD]
[/TR]
[TR]
[TD]511VSB2200770000111[/TD]
[TD]511VSB2200770000111[/TD]
[TD][/TD]
[TD]otherplace[/TD]
[TD]otherthing[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]511VSB2200770000356[/TD]
[TD]511VSB2200770000356[/TD]
[TD]Place[/TD]
[TD]thing[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
becomes
[TABLE="width: 500"]
<tbody>[TR]
[TD]Original ID[/TD]
[TD]Master ID List[/TD]
[TD]Up to Date ID[/TD]
[TD]Nomenclature[/TD]
[TD]Part Description[/TD]
[/TR]
[TR]
[TD]511VSB2200770000111[/TD]
[TD]511VSB2200770000111[/TD]
[TD][/TD]
[TD]otherplace[/TD]
[TD]otherthing[/TD]
[/TR]
[TR]
[TD]511VSB2200770000356[/TD]
[TD]511VSB2200770000356[/TD]
[TD]511VSB2200770000356[/TD]
[TD]Place[/TD]
[TD]thing[/TD]
[/TR]
</tbody>[/TABLE]
5. The table should now have no duplicate instances of the Master ID now, but the final step should be to resort the table based on the last 8 digits of the Master ID list, finally looking something like this.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Original ID[/TD]
[TD]Master ID List[/TD]
[TD]Up to Date ID[/TD]
[TD]Nomenclature[/TD]
[TD]Part Description[/TD]
[/TR]
[TR]
[TD]123ABC1234500000001[/TD]
[TD]123ABC1234500000001[/TD]
[TD]123ABC1234500000001[/TD]
[TD]place[/TD]
[TD]screw[/TD]
[/TR]
[TR]
[TD]123ABC1234500000003[/TD]
[TD]123ABC1234500000003[/TD]
[TD]123ABC1234500000003[/TD]
[TD]otherplace[/TD]
[TD]screw[/TD]
[/TR]
[TR]
[TD]123ABC1234500000004[/TD]
[TD]123ABC1234500000004[/TD]
[TD][/TD]
[TD]australia[/TD]
[TD]bolt[/TD]
[/TR]
[TR]
[TD]123ABC1234500000009[/TD]
[TD]123ABC1234500000009[/TD]
[TD][/TD]
[TD]themoon[/TD]
[TD]thing[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]123ABC1234500000020[/TD]
[TD]123ABC1234500000020[/TD]
[TD]thatplace[/TD]
[TD]thing[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]123ABC1234500000100[/TD]
[TD]123ABC1234500000100[/TD]
[TD]neverland[/TD]
[TD]thing[/TD]
[/TR]
[TR]
[TD]123ABC1234500000999[/TD]
[TD]123ABC1234500000999[/TD]
[TD]123ABC1234500000999[/TD]
[TD]nowhere[/TD]
[TD]plank[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]123ABC1234500123456[/TD]
[TD]123ABC1234500123456[/TD]
[TD]themoon[/TD]
[TD]goose[/TD]
[/TR]
[TR]
[TD]123ABC1234500123457[/TD]
[TD]123ABC1234500123457[/TD]
[TD][/TD]
[TD]somewhere[/TD]
[TD]duck[/TD]
[/TR]
</tbody>[/TABLE]
