Solved

Excel VBA data concatenation

Posted on 2013-10-24
20
333 Views
Last Modified: 2013-11-20
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
0
Comment
Question by:tesla764
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 7
20 Comments
 

Author Comment

by:tesla764
ID: 39597564
Basic logic flow...
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)
0
 
LVL 32

Assisted Solution

by:Robberbaron (robr)
Robberbaron (robr) earned 480 total points
ID: 39600216
1. so you will have lots of columns to process ?  eg beyond Z

2. And only one row or a number of rows as well ?

3. do you want this to happen as you change cells or is it ok to have a button <Update Now>

please post a test workbook rather than word screenshots.
0
 
LVL 15

Assisted Solution

by:Berkson Wein
Berkson Wein earned 20 total points
ID: 39601369
I also agree, please post an excel sheet.  Your description isn't particularly clear.  Seeing the actual xlsx document will help for sure.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:tesla764
ID: 39605501
0
 

Author Comment

by:tesla764
ID: 39605510
If you put all 3 attached files in c:\temp this should work.
DRA-PDS-Audit.accdb
0
 

Author Comment

by:tesla764
ID: 39605730
Workbook and db files have been provided. Do they work alright for you?
0
 
LVL 32

Assisted Solution

by:Robberbaron (robr)
Robberbaron (robr) earned 480 total points
ID: 39608273
had to make some changes to the hardcoded paths but get it to open.

do you need the function to occur upon workbook open like the other code or by a button on the sheet.  I'll attempt the later as should be able to be independent of startup.
0
 

Author Comment

by:tesla764
ID: 39608527
That's fine.
0
 
LVL 32

Assisted Solution

by:Robberbaron (robr)
Robberbaron (robr) earned 480 total points
ID: 39611253
I cant get the Country recordset to populate.... no ISAM yet the question list does.

so i cant get any data to match your example..

can you save the example in the doc file as values only ?

copy all the cells on the sheet and then paste-as-values to a new sheet.
0
 
LVL 32

Assisted Solution

by:Robberbaron (robr)
Robberbaron (robr) earned 480 total points
ID: 39611275
is AggregateColumnValues the function you want extended ?

it seems to be joining cells based upon color.
0
 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 480 total points
ID: 39611289
please check my logic...

start at row 3, Column B.  (cellStart)
   find the next cell in Row 3 that has green background. RGB(220, 230, 241) (cellEnd)
      start a new dataConcatenation=""

      for each [i]cellX [/i]in Row 4 up to cellEnd
           if cellX is not part of the cellStart columnspan (coloured) then 
                if cellX value is "Regulatory", add value to  dataConcatenation
      next cellX
      
     write dataConcatenation value to cell above [cellStart]

  loop until end of row.

Open in new window

0
 

Author Comment

by:tesla764
ID: 39614759
Yes your logic is correct.
0
 

Author Comment

by:tesla764
ID: 39614767
The actual Green color is...
RGB(153, 255, 51)
0
 

Author Comment

by:tesla764
ID: 39614792
I can provide you with the workbook and a scaled down version of the DB if you would prefer.
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39616350
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.
0
 

Author Comment

by:tesla764
ID: 39616593
This is the workbook that is the result of selecting the criteria.
DRA-Summary.xlsx
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39618328
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
0
 

Author Comment

by:tesla764
ID: 39622030
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
0
 

Author Closing Comment

by:tesla764
ID: 39663541
Thanks everybody for your participation.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question