Creat or use a macro to merge data into one cell as a list

[anking]

So basically I have a very large amount of data that I will probably have to run this macro several times for different sections if i'm undesrtanding how they work. I'm trying to consolidate postal codes into one cell for one county, city. Here is an example of what i'm trying to do:

Lincolnshire Grimsby DN31
Lincolnshire Grimsby DN32
Lincolnshire Grimsby DN33
Lincolnshire Grimsby DN34
Lincolnshire Grimsby DN36
Lincolnshire Grimsby DN37
Lincolnshire Grimsby DN41

to

Lincolnshire Grimsby DN31;DN32;DN33;DN34;DN36;DN37;DN41

Can this be done? I'm not very experienced with macros but i'm trying to learn.

Also I'm using Excel 2010

[watersev]

post sample workbook

[anking]

Here is what I have so far. I've been inputting it manually for now. Thank you for looking into it i very much appreciate it!!!

[anking]

Can this be done or do can it only be done manually?

[watersev]

see attachment, run macro "UK_PC"

[jaslake]

Hi anking

Try this

Please Login or Register  to view this content.

John

[anking]

WOW! watersev, that worked beautifully! I guess I don't understand the Macro language well enough because that blows me away. I looked at the code and tried to duplicate it myself but fail on my part. Thank you very much for everyone's time and effort.

Can you please direct me to a site that will help me learn how to create and use macros more effectively?

[anking]

I'm sorry to avail upon you again, but now I am trying to copy the macro to another workbook. with the macro wb open and the target wb open, I go into the developer tab for the macro wb, click visual basic, project explorer, drag the module to the new wb, and it appears there. So i ran the macro in the target wb and it starts but it has been sitting with a blank screen for a while now with an hourglass. am i doing something wrong or does this mean that it is working?

[anking]

I tried this macro on another workbook and it said there was a run time error '6' went to debug and it had this highlighted:

For i = LR To 2 Step -1

[jaslake]

Hi anking

You don't indicate to whom you're speaking bit I know the line of code you reference is in the code I provided. I don't know if that same line is in watersev's code. Her/His file is a zip file and I chose not to download it.

So, if you're speaking to me, the code worked for me on your original sample file. If it doesn't work

on another workbook

then I suspect the other workbook is different.

If you care to post a sample, I'll be glad to look at it.

John

[anking]

Sorry, i guess I should do that jaslave

basically i have a list to make for the uk which has been taken care of, one for the us, one for canada, and one for australia. If you can look at this and tell me how to modify the code for the other books that would be wonderful. i'm absolutely stumped and i can't seem to get this! thanks again for all of your help

[jaslake]

Hi anking

I'll look at the differences in the files and recommend changes. No file(s) attached to your last post.

John

[anking]

oops i've been at this for 8 hours straight guess i'm getting sloppy. sorry jaslake

here they are

[jaslake]

Hi anking

Your UK file doesn't have headers, the other two do. Will UK have headers also?

John

[anking]

Actually I'll probably be adding headers back in to the UK file, but for ease of use if deleting headers is need then I am all for it. I can fix it once the data has been merged.

[jaslake]

Hi anking

Consistency is best; I'll add headers to UK. You can change the description to what you need, won't matter.

The US file has a different structure so it will take slightly different coding. Canada's file layout is the same as UK so the coding is the same.

I'll post later tonight with the code for each file.

Don't know how often you do this or how involved you wish to get. Can make it so each file runs independently or you can choose which file to run. If you choose which file to run, it'll run the procedure appropriate for that file.

You let me know.

John

[jaslake]

Hi anking

The code for Canada is the same as the code for UK. The code for US is

Please Login or Register  to view this content.

You didn't post a file for Australia so I've nothing for that.

Let me know of issues.

John

[anking]

Thank you jaslake. This is actually the first time i've tried to make anything like this work. i'm used to using formulas and certain functions/tools in Excel but I don't have much experience in macros. i'm seeing that is something I need to learn to truly be able to use Excel to the point I need to. I want to learn how to understand and create macros so I can do more with this kind of thing.
i also have another one i am trying to work up. i was hoping i could use the macros that were already made up by you guys but i guess it's not wanting to work like that. basically they can be separate. they will all be inputted to a PHP after i can get the formatting right.
the last one for Australia is attached. hopefully it will be the same as one of the others so there is less work to do.

If you know of any good teaching tools or websites to learn how to create macros can you forward me the link so i can start learning it well?

P.S. Everything you are doing is very much appreciated. It's amazing what you guys can do.

[jaslake]

Hi anking

Australia looks the same as UK and Canada, only three columns of data. So the UK/Canada code should work for Australia.

US has four columns of data so the code had to be modified to support the extra column.

Let me know of issues.

John

[anking]

I guess i'm doing something wrong because when i copy the code and run the macro for the Canada and Australia workbooks it basically freezes or something, it says it's not responding and then shuts itself down

[anking]

Also when i try to run the USA macro i get a message that says overflow

[jaslake]

Hi anking

I'll attach the workbooks for each country that I've used for testing. Give me a few moments.

John

[jaslake]

Hi anking

Attached are files for UK, Canada and US. These are working in Excel 2007 on my platform. Don't have Excel 2010 but I can't imagine there'd be compatibility issues.

I'll need to work on Australia. This file has numeric characters in column A; it'll take yet a different approach.

The three attached files will give you something to play with while I play with Australia. I believe each file has a copy of your original data in sheet 2. You can run the procedure on sheet 1. If you wish to rerun, simply copy/paste sheet 2 to sheet 1.

It's getting late here so may or may not get back to you tonight.

John

[jaslake]

Hi anking

In looking at Australia, I see that it's totally different. I'm attaching a file with just some Australian data. I'd like you to look at sheet1. Enter into sheet 2 what you expect to see after the data is manipulated then post the results.

I could guess but guessing is not good.

John

[anking]

I don't know why but the first time i opened a sample and ran the macro in the sample it worked, but when i copied it to the full spreadsheet it didn't work. i keep getting the same errors. and now, all that shows up when i open the file is a picture?

i am completely confused. i'm sorry. i don't know why it's not working.

[anking]

sorry about that, i didn't format the columns correctly before sending it to you. i think i corrected that.

can a change in the amount of data on the spreadsheet cause the macro not to work?

[anking]

I'm going to go ahead and knock off for the night. It's late here so I know it's late there. Get some rest and thank you for what you are doing. I'll get back on tomorrow morning when i wake up.

[watersev]

see attachment files.

Run the only macro in the file. The difference from the code of jaslake is that in my code there are no duplicates in result table and data can be unsorted (as in Canada for example).

[anking]

Watersev, thank you for the new macros. I am still having problems though. i don't understand what i'm doing wrong. the macro runs in the sample no problem, but when i copy it over to the full spreadsheet and try to run it, it doesn't work. excel ends up freezing/not responding. what am i doing wrong?

[jaslake]

Hi anking

After reconfiguring the columns for Australia, the UK/Canada code works for Australia.

Please Login or Register  to view this content.

I haven't checked each line item but as far as I can tell, no duplicates exist.

As to this

i don't know why it's not working.

I'd really have no way of knowing without looking at your real data file.

John

[anking]

jaslake, i posted samples because the actual spreadsheets are way too big to post on here. i don't understand why the macros work in the sample but not in the original spreadsheets. i tried converting the files from docx and putting them into 2003 format and running it then, but that didn't work either.

[jaslake]

Hi anking

I don't understand, your working with Word documents?

John

[anking]

sorry not docx, used to saying that. the xls formats

[jaslake]

Hi anking

Have you tried zipping the file?

John

[watersev]

what am i doing wrong?

what is the difference between sample workbook and your workbook? can you post it zipped?

[anking]

okay guys, i've zipped the files but the us and canada files are still too big even zipped so here is australia

[anking]

the only difference that i can think of is file size

[jaslake]

Hi anking

Code runs fine on my platform. See attached.

John

[anking]

jaslake, That's what i don't understand. it HAS to be something i'm doing or not doing. The spreadsheet looks great by the way. Did you have to change the file format or anything? How did you copy the macro over? i have been opening the two wb, going to VB in the wb with the macro, then project explore and dragging the macro to the other wb. after that, whether i close the VB and macro wb or leave them all open, when i try to run the macro on the other wb it does that weird freezing thing. I just don't get it.

I need to find out what i'm doing wrong somehow because i have those other two and i can't post them here for some reason. size i think but i'm not sure.

[anking]

okay so when i'm opening the files they show up as file name [read only] could that be causing the problem?

[jaslake]

Hi anking

Could very well be but you should get an error message when you try to change something. Change the file property and see what happens.

I didn't need to change the file format (although when I saved it I saved as .xlsm). To insert the macro in your downloaded file, I inserted a new module and then did a copy/paste of the code. Nothing magic.

Can't begin to guess what the issue is. Can't believe it would be a 2010 issue.

John

[anking]

hey jaslake

well, i saved one of the read only files from xlsm to xls, tried running the macro that way, and then i get the overflow error message which the help says this about:

Overflow (Error 6)
An overflow results when you try to make an assignment that exceeds the limitations of the target of the assignment. This error has the following causes and solutions:

The result of an assignment, calculation, or data type conversion is too large to be represented within the range of values allowed for that type of variable.
Assign the value to a variable of a type that can hold a larger range of values.

An assignment to a property exceeds the maximum value the property can accept.
Make sure your assignment fits the range for the property to which it is made.

You attempt to use a number in a calculation, and that number is coerced into an integer, but the result is larger than an integer. For example:
Dim x As Long
x = 2000 * 365 ' Error: Overflow

To work around this situation, type the number, like this:

Dim x As Long
x = CLng(2000) * 365

if i'm reading that correctly it is saying it is due to the size difference?

[anking]

the actual spreadsheet i need to use this macro with has 65536 rows. would that be the problem?

[jaslake]

Hi anking

Change this line of code

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

See if that resolves the issue.

John

[anking]

jaslake

Okay, we're very close now. that last fix worked for the USA spreadsheet but I got this run time error '1004' when I ran it for the Canada sheet. Here is what help said about it:

Application-defined or object-defined error
This message is displayed when an error generated with the Raise method or Error statement doesn't correspond to an error defined by Visual Basic for Applications. It is also returned by the Error function for arguments that don't correspond to errors defined by Visual Basic for Applications. Thus it may be an error you defined, or one that is defined by an object, including host applications like Microsoft Excel, Visual Basic, and so on. For example, Visual Basic forms generate form-related errors that can't be generated from code simply by specifying a number as an argument to the Raise method or Error statement. This message has the following causes and solutions:


Your application executed an Err.Raise n or Error n statement, but the number n isn't defined by Visual Basic for Applications.
If this was what was intended, you must use Err.Raise and specify additional arguments so that an end user can understand the nature of the error. For example, you can include a description string, source, and help information. To regenerate an error that you trapped, this approach will work if you don't execute Err.Clear before regenerating the error. If you execute Err.Clear first, you must fill in the additional arguments to the Raise method. Look at the context in which the error occurred, and make sure you are regenerating the same error.

It may be that in accessing objects from other applications, an error was propagated back to your program that can't be mapped to a Visual Basic error.
Check the documentation for any objects you have accessed. The Err object's Source property should contain the programmatic ID of the application or object that generated the error. To understand the context of an error returned by an object, you may want to use the On Error Resume Next construct in code that accesses objects, rather than the On Error GoTo line syntax.


Note
In the past, programmers often used a loop to print out a list of all trappable error message strings. Typically this was done with code such as the following:


For index = 1 to 500
Debug.Print Error$(index)
Next index


Such code still lists all the Visual Basic for Applications error messages, but displays "Application-defined or object-defined error" for host-defined errors, for example those in Visual Basic that relate to forms, controls, and so on. Many of these are trappable run-time errors. You can use the Help Search dialog box to find the list of trappable errors specific to your host application. Click Search, type Trappable in the first text box, and then click Show Topics. Select Trappable Errors in the lower list box and click Go To.



the debug program in VB highlighted the following code:

Rng(i).Offset(-1, 2).Value = Rng(i).Offset(-1, 2).Value & ";" & Rng(i).Offset(0, 2).Value

[jaslake]

Hi anking

I can't duplicate the error. Do UK and Australia run OK? It's the same code. If these run OK then make a copy of your "Live" Canada file (including the code). Delete records to reduce the size of the file. Post this copy to your thread.

John

[anking]

The "live" canada file has 765345 rows, and the one i'm posting has less than 9000 rows

[anking]

Option Explicit
Public Sub CombinePostalCodes()
Dim Rng As Range
Dim LR As Long
Dim i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Set Rng = Range("A1:A" & LR)
With Rng
For i = LR To 2 Step -1
If Rng(i).Value & Rng(i).Offset(0, 1).Value = Rng(i).Offset(-1, 0).Value & Rng(i).Offset(-1, 1).Value Then
Rng(i).Offset(-1, 2).Value = Rng(i).Offset(-1, 2).Value & ";" & Rng(i).Offset(0, 2).Value
Rng(i).EntireRow.Delete
End If
Next
End With
Application.ScreenUpdating = True
End Sub


That is the macro being used

[jaslake]

Hi anking

Please wrap your code in code tags.

The file you posted does not contain any macros. What I wish to do is emulate your environment as must as is possible. That's why I asked you to make a copy of your live Canada file, including any code that resides in the live Canada file.

Yes, I can copy the code you posted into the file you posted but then it's not a real copy of your live file, it's manufactured.

Do the UK/Australia files perform as expected????

John

[anking]

The other two worked after using the fix Dim i As Long, but now when i try using it for the canada database i get a error code there is a big red x (like to close the program) 400 in the error message and that is all there is. it won't direct me to help like the others would.

i'm going to upload the macro canada 1 file again. when i open the file and go to view code it shows there is a macro there. let's hope this works.

[jaslake]

Hi anking

Can't duplicate your error but, a couple of things.

The code should be in a general module not in the sheet module. This is NOT causing your problem.

I increased the data records to about 220,000 rows and ran the procedure. It did execute but just ran and ran; I didn't wait for it to finish. So, I believe the problem is that the file is too large for this particular code.

I'll need to rethink this and see if I can come up with a different approach. I'll let you know ASAP.

John

[anking]

Thank you for all you're doing jaslake

I've started taking the database and saving it into sections, it's probably going to take about 12 or 13 different files, because the macro worked on a smaller amount of data I am going to try running the macro on all of those files and then just rebuilding the final database from the pieces.

I don't know if it will work but that may be the only way to get this one completed.

[jaslake]

Hi anking

I thought of that as a possibility. While you work that angle, I'm working an another angle.

Keep me posted.

John

[anking]

I ended up having to split it into 14 files. i'm running the macro on the 4th one now, but so far they have been working beautifully. i think after i get all of them done all i'll have to do is copy and paste them all together again into one file.

[jaslake]

Hi anking

Sounds like you're making progress. Is this a one time exercise or will you need to process these country regularly?

John

[anking]

jaslake

As far as I know this is a one time thing. i don't see too much more of this kind of work coming my way but you can never really be sure. Either way, i need to start learning how to make and manipulate macros for myself. where did you start?

[anking]

Jaslake,

thank you again for all of your help. Your final codes worked perfectly and the Canada database was just too big. I ended up breaking it down even more and running the macro and when all said and done it worked great

Thank you for everything!!!

[jaslake]

You're welcome.

John