Count Comma Delimited values in Cell, Convert to rows, Repeat Remaining info next to Rows

**avpowar** · 01-28-2016, 05:42 AM

Hi,
I'm having a problem in VBA..
What I want to do:
- I have workbooks with table which has (let's say) 5 columns. Formatting is same throughout the 1000 workbooks.
- 3rd column gives the ID No. to which the remaining values (from column 1,2,4,5) are linked
- Sometimes multiple IDs are given to which the remaining data is same and needs to be repeated in master workbook.

* Now i have figured out how to copy data row after row from all of the workbooks if the ID given in cell is single. But whenever multiple IDs appear in 3rd Column cell (Comma separated IDs ), then I don't know how to copy them in master workbook in each row and likewise copy the contents of Column 1,2,4,5 for all of the IDs.

* Now i have logic as below, but being a fresher engineer my VBA skills are not up to par for this task.
*** Logic : separate the comma separated values in cell somehow
count and copy the values to rows
copy remaining data the no. of times found in count or until found next empty row

For next or do while inside if else maybe required.

Can anyone show me the program to do so??? I'll be grateful and thanks in advance to all who'll help. thank you.

**Richard Buttrey** · 01-28-2016, 05:50 AM

Hi and welcome to the forum.

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results, some explanatory notes as well.

**avpowar** · 01-28-2016, 06:53 AM

Sample.zip

Hi Sir,
Thank you for replying so soon.
I've created sample sheets which you may call mini copies of the originals. (Sorry , can't upload original, will risk my job as a fresher uploading company data to public site)

Attached Zip contains 3 village files and the master workbook to which the data is to be copied.
I've done the copying for Village 1 and Village 3. But Can't update for village 2.(I can but it copies all values in one row opposed to what i want)

Master workbook is the result I want From Village 1,2,3.

Since i manually created this one. I'll Reply you the code i'm trying in 5 minutes sir.
Please wait.

**avpowar** · 01-28-2016, 07:29 AM

Master with results from my code.xlsm

These are my results and you'll see the problem i'm Facing..
The data files are in different folders than master workbook though.
In sample.Zip I sent in one folder for convenience.
Thank you Sir in advance.

**avpowar** · 02-02-2016, 05:37 AM

anyone going to help??

**Richard Buttrey** · 02-02-2016, 08:22 AM

Hi,

This is very confusing. Your macro refers to a sheet in the Master Workbook called "Model History" which doesn't exist. Please clarify.

**avpowar** · 02-02-2016, 11:55 PM

That's Sheet 2.. Sorry For that Sir.

**avpowar** · 02-03-2016, 05:37 AM

Sample Files.zip

Attached File Contains 3 Village Files in a folder and 2 master files (one is expected and another is the result I get) . in master workbook's code, i give path to the folder where the village files are kept. then the code copies data from model history sheet of each village workbook and pastes it in sheet2 of master workbook.

**Richard Buttrey** · 02-03-2016, 10:45 PM

Hi,

Try this instead

Please Login or Register  to view this content.

**avpowar** · 02-04-2016, 08:23 AM

Hi Sir,
This worked flawless .. But when I modify it for my needs it copies the data without separating the comma separated values.
I figured out the problem was that the cells having values that needed to be separated were referenced from another sheet. So I tried it on one sample where I basically copied the cells and pasted them as values in same place manually. then it worked. but it is nearly impossible to do this for large amount of sheets. So how to make it work for referenced cells?

Currently my comma separated cells are in position B10 and B11
and needs to be copied at H and I. So I changed it accordingly.
and the sheets from which i am copying are macro enabled (if that matters.)

Following is my new code for large data.

Please Login or Register  to view this content.

Count Comma Delimited values in Cell, Convert to rows, Repeat Remaining info next to Rows

LinkBack

Thread Tools

Rate This Thread

Display

Count Comma Delimited values in Cell, Convert to rows, Repeat Remaining info next to Rows

Re: Count Comma Delimited values in Cell, Convert to rows, Repeat Remaining info next to R

Re: Count Comma Delimited values in Cell, Convert to rows, Repeat Remaining info next to R

Re: Count Comma Delimited values in Cell, Convert to rows, Repeat Remaining info next to R

Re: Count Comma Delimited values in Cell, Convert to rows, Repeat Remaining info next to R

Re: Count Comma Delimited values in Cell, Convert to rows, Repeat Remaining info next to R

Re: Count Comma Delimited values in Cell, Convert to rows, Repeat Remaining info next to R

Re: Count Comma Delimited values in Cell, Convert to rows, Repeat Remaining info next to R

Re: Count Comma Delimited values in Cell, Convert to rows, Repeat Remaining info next to R

Re: Count Comma Delimited values in Cell, Convert to rows, Repeat Remaining info next to R

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Multiple cells comma delimited to rows

[SOLVED] Comma delimited data in multiple cells to rows

[SOLVED] comma and dash delimited data to rows

Separate comma-delimited values to rows

Separate comma delimited values to rows

SUM of values in a comma delimited cell

Comma Delimited to Rows, Over 255 Records

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions