tesla764
asked on
Excel VBA data concatenation
Hi,
I would like to get assistance with VBA coding to perform data concatenation in an Excel spread sheet.
Please see the attached file for problem statement and screen shots. Please let me know if you need more clarification or have any
questions.
Thanks in advance.
EE-Data-concatenation.docx
I would like to get assistance with VBA coding to perform data concatenation in an Excel spread sheet.
Please see the attached file for problem statement and screen shots. Please let me know if you need more clarification or have any
questions.
Thanks in advance.
EE-Data-concatenation.docx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
If you put all 3 attached files in c:\temp this should work.
DRA-PDS-Audit.accdb
DRA-PDS-Audit.accdb
ASKER
Workbook and db files have been provided. Do they work alright for you?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That's fine.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes your logic is correct.
ASKER
The actual Green color is...
RGB(153, 255, 51)
RGB(153, 255, 51)
ASKER
I can provide you with the workbook and a scaled down version of the DB if you would prefer.
1/ just the workbook, with data in it. Copy and paste <as-values> to leave out the db. we can then work on the aggregation in isolation.
2/ the color is just from the current code.
2/ the color is just from the current code.
ASKER
This is the workbook that is the result of selecting the criteria.
DRA-Summary.xlsx
DRA-Summary.xlsx
here is my test1 , i am missing part of your request i guess.
1/ is the summary supposed to be just one of each text found ?
2/ is it supposed to be all cells in the 'Regualtory' column ? ie multiple rows ?
can you draw arrows from the cells that are to make up the summary ?
DRA-Summary1.xlsm
1/ is the summary supposed to be just one of each text found ?
2/ is it supposed to be all cells in the 'Regualtory' column ? ie multiple rows ?
can you draw arrows from the cells that are to make up the summary ?
DRA-Summary1.xlsm
ASKER
Here is a simplified, straight forward synopsis of this request...
Background...
The cells in row 3, the green fill color is RGB(153,255,51)
The cells in row 4, the yellow fill color is RGB(255,255,0)
Note - In this screen shot the TEXT in the 1st green cell is
"GQ-005 - Approval Time"
and the next green cell has
"GQ-008 - GMP Certificate"
Process…
In the area BETWEEN the cells that have the green RGB(153,255,51), in this case Columns D to W...
I want to copy the value in column "E" (Not Applicable) to column "A"
Then copy the value in column "O" (3 Months) to column "A" (appended)
• Note that the column data that gets appended has the word "Regulatory" in the header with the yellow fill color.
• Note that there is a third occurrence of "Regulatory", the data in that column contains "3 Months" which has already been appended, there should be no duplicate data that gets appended.
Please refer to the spreadsheet that is attached “GQ Spreadsheet.xlsx” when reading this problem statement.
Hopefully this is clear. If not please ask any questions you may have.
Thanks for your help in advance.
GQ-Spreadsheet.xlsx
Background...
The cells in row 3, the green fill color is RGB(153,255,51)
The cells in row 4, the yellow fill color is RGB(255,255,0)
Note - In this screen shot the TEXT in the 1st green cell is
"GQ-005 - Approval Time"
and the next green cell has
"GQ-008 - GMP Certificate"
Process…
In the area BETWEEN the cells that have the green RGB(153,255,51), in this case Columns D to W...
I want to copy the value in column "E" (Not Applicable) to column "A"
Then copy the value in column "O" (3 Months) to column "A" (appended)
• Note that the column data that gets appended has the word "Regulatory" in the header with the yellow fill color.
• Note that there is a third occurrence of "Regulatory", the data in that column contains "3 Months" which has already been appended, there should be no duplicate data that gets appended.
Please refer to the spreadsheet that is attached “GQ Spreadsheet.xlsx” when reading this problem statement.
Hopefully this is clear. If not please ask any questions you may have.
Thanks for your help in advance.
GQ-Spreadsheet.xlsx
ASKER
Thanks everybody for your participation.
ASKER
Check row 3 - Find cell that has Green fill color.
Next column
if row 3 cell is not Green then
check row 4 for "Regulatory"
When "Regulatory" is found
copy (concatenate) the value in that column data to the column to the left (Column with Green fill in row 3), that has the word "Regulatory"
This concatenation will take plae until the Green fill color is encountered (row 3)