Multiple rows from a single record?

**artistdedigital** · 11-10-2014, 11:54 PM

Hi all excel gurus,

I have a large dataset where I have a column of authors where the authors are split based on a semicolon; Now I need to split all these authors into an individual rows of record along with all the other data relative to that record should duplicate.

Is there some way I can make excel to look for the semicolon ; and split it to a separate row with all the other data kept intact?

Can someone please help me how can I achieve that? Attached a sample spreadsheet for your reference. Thanks in advance.

**stnkynts** · 11-11-2014, 12:50 AM

Please Login or Register  to view this content.

**artistdedigital** · 11-11-2014, 07:16 PM

Originally Posted by stnkynts

Please Login or Register  to view this content.

Hi stnkynts,

Thanks a lot for the reply. I did open the excel worksheet and went to the developer tab and click Visual Basic. It opened a new window where in the General window I pasted the codes you provided and Run them. It asks for "Make the tempSheet" where I pressed "OK". But it doesn't do anything? Am I doing something wrong here? Sorry I am a total novice here

Thanks once more for your help mate.

**artistdedigital** · 11-11-2014, 07:47 PM

Hi stnkynts,

Sorry for the earlier post. Its my bad...the script is working perfectly now. Please disregard my previous reply. You are a saviour mate. Cheers and thanks once again.

**artistdedigital** · 11-11-2014, 09:02 PM

Hi stnkynts,

Thanks once again for the brilliant script.

Also, can you explain a little bit about the script and which value I have to modify it to work in various different worksheets? The script worked perfectly on the demo worksheet I provided on this thread but when I run the same script on the actual huge datasheet I have it throughs an error as : “Run-time error ‘9’: Subscript out of range”?

It would really be helpful if you explain a bit which fields I need to modify to get this magnificent script to work in different worksheet in the same scenario.

Thanks again mate. Cheers.

**stnkynts** · 11-11-2014, 09:13 PM

That is kind of like asking me to solve a cardboard puzzle when I can't see the image on any of the pieces. Telling you what each line does probably isn't going to help. If I were to guess as to the reason why you got the error it would be because you didn't change the value of ws1 from "Sheet1" to your actual source sheet (as notated in the code). That is just a stab in the dark.

Just another reason why it is recommended that posters submit an actual workbook with any sensitive information removed.

**apo** · 11-11-2014, 09:38 PM

Hi..

I just worked on this for last hour so I might as well post it as an alternative..

Tested it on 20 000 rows (replicating your sample data).. took about 1 second to complete.

Please Login or Register  to view this content.

**artistdedigital** · 11-11-2014, 11:01 PM

Hey guys,

I am so sorry for not posting the actual dataset

I am now attaching the actual dataset with reduced rows. In the actual dataset I have 23680 rows. Please notice the column "G and H". Once again, I really appreciate all your help. Cheers.

**apo** · 11-11-2014, 11:32 PM

Give this a go on your 'real' data set...

Tested on 25 000 rows took about 3 seconds..

Please Login or Register  to view this content.

**stnkynts** · 11-11-2014, 11:33 PM

Your example had column D as the author column while your real book has it as column G. You might have modified the code from column D to column G but you probably didn't edit the offset (a couple of lines down) to match appropriately.

What is this author ID column (column H)? Is this another critical piece of information that you left out of the original post? I am not sure what you want to do with it so I will guess.

Please Login or Register  to view this content.

**artistdedigital** · 11-12-2014, 06:33 AM

Hey Apo,

Thanks for your help. But when I try running the script it throws this error? "

Run-time error '13' Type mismatch

I am not quite sure why it does that??? Thanks.

But the one you attached is working perfectly? Can you please explain the script a little so that I can try understanding what's its doing internally? Thanks again.

**artistdedigital** · 11-12-2014, 06:44 AM

Hey dude,

Thanks for your constant help. But I am encountering error like this when trying to run the script? Error: "

Runtime error '9': Subscript out of range

" Not sure why?
Also, I understood that G and G2:G are the target range.

LR = ws1.Range("G" & Rows.Count).End(xlUp).Row

For Each rCell In ws1.Range("G2:G" & LR)

Also, "G" and "H" is the destination column

?

ws2.Range("G" & Rows.Count).End(xlUp) = Trim(vSplit(i))
ws2.Range("H" & Rows.Count).End(xlUp) = Trim(vSplit2(i))

But what this bit represents?

rCell.EntireRow.Copy ws2.Range("G" & Rows.Count).End(xlUp).Offset(1, -6)

Thanks a lot once more mate. Cheers.

**apo** · 11-12-2014, 07:39 AM

Not sure.. as you have seen.. my code works on your sample dataset..

See stnkynts post #6..

If your actual workbook is too big to attach here.. upload it to dropbox/mediafire/whatever and post the link here..

As an example.. here is a Workbook.. with 25 000 rows of raw data (replication of your sample set).. which results in ~90 000 rows outputted to sheet2..

Download link:

http://www.mediafire.com/download/8v...sj7r/Pubs.xlsm

Can you please explain the script a little so that I can try understanding what's its doing internally?

The best way.. is for you to Step through the code using F8 in the VB Editor.. make sure you have the Locals Window open (View>Locals Window)..

Expand the arrays as you step through the code..

Edit: Probably the only line of code that needs explaining that maybe won't be seen as you step through is this one..

Please Login or Register  to view this content.

I will try to explain what i was thinking:

I want to find the Upper Limit of the first dimension of the y array... so...

* The values in column 7 are delimited by a ";"... so Transpose and JOIN the values using the VBLF.. so then i end up with a really long string.. but.. because the whole point of doing this is to ultimately find out how many ";" there are (and a ";" should be between each name.. i need to replace the vblf with a ";".... so then... i will basically end up with a 'variant/string' that has a ";" between every value in column 7.

* Next.. I split that 'variant/string' by the ";" delimiter which creates a 1 Dimensional Array with each value (Name) as an element... so.. next up is to find the Upper Bound of that array and bada bing bada boom.. we have the Upper Bound needed to dimension the first dimension of the y array.

Trust me.. imho.. the Locals Window is like GOLD.

Edit: Thanks to advice from romperstomper.. "Join" returns String not array..

**artistdedigital** · 11-12-2014, 06:48 PM

Thanks apo...you inspired me to dig into excel VB

cheers your effort mate...I noticed you are from NSW, australia? I am from Sydney too....I owe you a beer now mate...hahahahahahha

**apo** · 11-12-2014, 06:57 PM

No worries.. I'll drink to that..

btw.. i assume you got it working on your real Workbook?

**artistdedigital** · 11-12-2014, 07:03 PM

Well, kind of

The codes you wrote are completely correct...I just have to figure out how to create the "Command Button" and connect the codes to it??? Otherwise on the actual workbook its still showing type mismatch error...I think still looking for the "CommandButton1" and click() on the top bar in the VB edit window? But in my actual workbook its still showing "General"...

**apo** · 11-12-2014, 07:20 PM

k.. do this:

1. Make sure the "Developer Tab" is enabled.. if it is.. it will be the next Tab along form "View".. if it isn't.. go to Excel Options>Customize Ribbon and Enable the Developer Tab.

2. Then.. on the Developer Tab.. click the "Insert" dropdown and Select the Active X Command Button.. move the cursor to where you want it on your sheet and left click.

3. Then.. double click on the Command Button you just placed and place the code i posted in between the Private Sub CommandButton1_Click() and End Sub text..

4. When you want to actually use the button.. make sure "Design Mode" on the Developer Tab is not active..

**artistdedigital** · 11-13-2014, 05:47 AM

Thanks apo...cheers....really appreciate your help...its finally working fine...

**artistdedigital** · 11-13-2014, 05:49 AM

@stnkynts

Your example had column D as the author column while your real book has it as column G. You might have modified the code from column D to column G but you probably didn't edit the offset (a couple of lines down) to match appropriately.

What is this author ID column (column H)? Is this another critical piece of information that you left out of the original post? I am not sure what you want to do with it so I will guess.

Thaks man...yes, its also a important piece of data...but I was thinking once I understand the script I will modify it to my needs...

Multiple rows from a single record?

LinkBack

Thread Tools

Rate This Thread

Display

Multiple rows from a single record?

Re: Multiple rows from a single record?

Re: Multiple rows from a single record?

Re: Multiple rows from a single record?

Re: Multiple rows from a single record?

Re: Multiple rows from a single record?

Re: Multiple rows from a single record?

Re: Multiple rows from a single record?

Re: Multiple rows from a single record?

Re: Multiple rows from a single record?

Re: Multiple rows from a single record?

Re: Multiple rows from a single record?

Re: Multiple rows from a single record?

Re: Multiple rows from a single record?

Re: Multiple rows from a single record?

Re: Multiple rows from a single record?

Re: Multiple rows from a single record?

Re: Multiple rows from a single record?

Re: Multiple rows from a single record?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Need Macro to move multiple rows into a single row for each 'Record ID'

Convert multiple x rows for a record to single - vertical to horizontal data

Problem with list where a single record has data in several rows instead of one row

Combining Multiple Rows into a Single Record

to make a single row record become multiple row records

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions