Hi,
I work for a printing company and we recieve a weekly .csv database that provides us the data to print and fulfil a mailing campaign. The number of records vary each week (2500 to 6000 +).
We have to validate certain fields of data and "suppress" the fields that don't meet the criteria. We then need to sort the data on several columns in turn, to select the 5 x longest and shortest records in specific cells to provide proofs with.
We then need to add a common reference to a cell on each row & also sequentially number so we can track each individual record through production.
We currently have a template that we copy and paste the .csv data into, it has conditional formatting and preset formulas to speed up the process, however there is still manual intervention that I think could be eliminated by using a macro..... The only problem is, I don't know how to write it!
So I am cap in hand asking for someone to help me with a macro that will do the following:
I would like to be able to press a button within the worksheet that will execute the macro, the 1st stage will open a browsing box so the user can select the .csv file to import.
Once this is selected, there will be a prompt asking the user to enter a "DM Number", this will be the common reference added to each row of data.
The .csv file’s columns are formatted in the following way:
A – M_MDBKey
B - M_SRec_ID
C – Agree no
D – Title
E – Forenames
F – Initials
G – LastNm
H - QAS_Add_Ln_1
I - QAS_Add_Ln_2
J - QAS_Town
K - QAS_Area
L - QAS_Pcode
M - Street_Nm
I would like the original, unprocessed data to be inserted into a worksheet in the workbook named “Original” to retain the initial data.
A copy of this data will also be inserted into a “working data” worksheet.
I would then like to validate the data in “working data” to create a “suppression” list, the criteria to suppress records is as follows:
· If the Title cell is blank
· If the length of the QAS_Pcode & Street_Nm – 3 (e.g =LEN(L2&M2)-3 ) is more than 27
If the rows fail the criteria, they are removed from the “working data” worksheet, and inserted into a “suppression” worksheet.
The “working data” sheet now contains the relevant data, I would then like the “DM Number” that the user was prompted for initially to be inserted onto each row in a subsequent column (N), and a sequential number (starting from 1) to be added to the next column (O).
I would then need to copy the 5 x longest & shortest records from the following columns:
· G – LastNm
· L - QAS_Pcode
· H - QAS_Add_Ln_1
· QAS_Pcode & Street_Nm – 3 (e.g =LEN(L2&M2)-3 )
In addition to this anything with “SEED” in the: A – M_MDBKey, B - M_SRec_ID, C – Agree no fields.
These rows need to be copied and inserted into another worksheet named “proofs”
I understand that I am asking for a lot here but I would be very grateful if someone could help!
Many thanks,
Chris
I work for a printing company and we recieve a weekly .csv database that provides us the data to print and fulfil a mailing campaign. The number of records vary each week (2500 to 6000 +).
We have to validate certain fields of data and "suppress" the fields that don't meet the criteria. We then need to sort the data on several columns in turn, to select the 5 x longest and shortest records in specific cells to provide proofs with.
We then need to add a common reference to a cell on each row & also sequentially number so we can track each individual record through production.
We currently have a template that we copy and paste the .csv data into, it has conditional formatting and preset formulas to speed up the process, however there is still manual intervention that I think could be eliminated by using a macro..... The only problem is, I don't know how to write it!
So I am cap in hand asking for someone to help me with a macro that will do the following:
I would like to be able to press a button within the worksheet that will execute the macro, the 1st stage will open a browsing box so the user can select the .csv file to import.
Once this is selected, there will be a prompt asking the user to enter a "DM Number", this will be the common reference added to each row of data.
The .csv file’s columns are formatted in the following way:
A – M_MDBKey
B - M_SRec_ID
C – Agree no
D – Title
E – Forenames
F – Initials
G – LastNm
H - QAS_Add_Ln_1
I - QAS_Add_Ln_2
J - QAS_Town
K - QAS_Area
L - QAS_Pcode
M - Street_Nm
I would like the original, unprocessed data to be inserted into a worksheet in the workbook named “Original” to retain the initial data.
A copy of this data will also be inserted into a “working data” worksheet.
I would then like to validate the data in “working data” to create a “suppression” list, the criteria to suppress records is as follows:
· If the Title cell is blank
· If the length of the QAS_Pcode & Street_Nm – 3 (e.g =LEN(L2&M2)-3 ) is more than 27
If the rows fail the criteria, they are removed from the “working data” worksheet, and inserted into a “suppression” worksheet.
The “working data” sheet now contains the relevant data, I would then like the “DM Number” that the user was prompted for initially to be inserted onto each row in a subsequent column (N), and a sequential number (starting from 1) to be added to the next column (O).
I would then need to copy the 5 x longest & shortest records from the following columns:
· G – LastNm
· L - QAS_Pcode
· H - QAS_Add_Ln_1
· QAS_Pcode & Street_Nm – 3 (e.g =LEN(L2&M2)-3 )
In addition to this anything with “SEED” in the: A – M_MDBKey, B - M_SRec_ID, C – Agree no fields.
These rows need to be copied and inserted into another worksheet named “proofs”
I understand that I am asking for a lot here but I would be very grateful if someone could help!
Many thanks,
Chris
