Sorting data from one general column to multiple specific columns

[abuchan]

Hoping this is possible.
I have attached two sheets. Sheet 1 is considered 'Raw Data' which is how I get the text file from the County Assessor. They define each of the three columns with an identifier (Imp_pn_id2) which is a parcel number, imprv_det_type which defines what the improvement is, and calc_area which gives the size or area of the improvement. A specific parcel may have more than one of the same type improvement (ie two porches).

What I am hoping to achieve is displayed on Sheet 2 considered the 'Sorted Results'. The column names in this table are only the ones that were present in this specific 'Raw Data' table, as there are quite a few more in the ‘real’ file. I am hoping to have one row for each unique parcel number with the improvements sorted into columns. Where a parcel has more than one of the same type improvement, I have added them and presented the total. ie P10000549652 has two CPOR (Covered Porch) of 9 each which is represented as 18 in its respective column for Sheet 2 ‘Sorted Results’.

The specific file I took this sample from has approximately 250,000 rows and 70,000 unique parcel numbers.

While my experience is very limited, at best, I am hoping for a macro because of the file size.

Any help, direction, or advice is appreciated.

[rylo]

Hi

Have you tried using a PIVOT TABLE?

rylo

[abuchan]

No I have not. If you think that would be an efficient way to accomplish what I am hoping for, I will do some research in the am and see if I can make it work. My knowlege with Excel comes from what I have picked up from this site, which is much appreciated.

Thank you, I will give it a good look in the morning.

Alec.

[pike]

Hi abuchan,
you could use..
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste
To run the Excel VBA code:

Please Login or Register  to view this content.

Choose Tools | Macro | Macros
Select a macro in the list, and click the Run button

[pike]

or to add vaules with no entries

Please Login or Register  to view this content.

[abuchan]

Thank you, it worked wonderfully on the sample. I will try it this afternoon on the 'real' set of data. Do you suspect any memory limitations with the dictionary?

Thank you again, I will respond further, later today.

Alec.

[abuchan]

I couldn't wait until later, I had to try it. As I stated in the original post, this sheet has almost 250,000 rows. I ran the macro and it thought for about five seconds and the following window appeared: Run-time error '457': This key is already associated with an element of this collection. When I debugged it, the following code was highlighted:

Please Login or Register  to view this content.

Thank you for your effort, it is appreciated.

[abuchan]

I have run some tests on the 'real' file. With 100 rows, it seems to work well. With 1,000 rows, it seems to work well. With 5,000 rows, it seems to work well. With 6,000 rows, it gives me the error from above. Went back to 5,000 rows and it seemed to work well again.

I then ran the data from rows 5,000 to 6,000 and it seems to work well. I ran the data from rows 5,000 to 10,000 and it worked well. I ran the data from row 5,000 to 11,000 (6,000 rows) and it seemed to work well. Tried rows 5,000 to 20,000 and it seems to work well.

Now I am even more confused than before...

Your thoughts are appreciated.

[abuchan]

I completed some further testing. I was able to locate the rows which were causing the first issue. Once I eliminated them, it was able to run from 1 to 8,000 without issue. when I tried 1 to 10,000 it gave me the same error. The four lines I removed looked just like any of the others.

Line 5833 P10386251078 MA 2787
Line 5834 P10386251078 AGAR 506
Line 5835 P10386251078 CPOR 110
Line 5836 P10386251078 CPOR 48
Line 5837 P10386251078 MPS 80

I can put a link up to download the 'real' file if it helps.

I am using the lower of the two Macros as all the parcel numbers need to be represented.

Thank you.

[abuchan]

I have made the 'real' file available at :
http://www.vitalsoft.com/~bradk/SkagitSortMacro.zip
Thanks.

[rylo]

Hi

Ran this to about 50k record mark in your full file, and got no errors to that point.

Please Login or Register  to view this content.

rylo

[abuchan]

I just finished running the Macro. 3 hours, 8 minutes, 56 seconds. After a slight panic attack, I located the results on Sheet 3. Upon first glance, I am very optimistic. I will continue to review it tonight.

Thank you very much.

Alec.

[abuchan]

It seems to work very well. I went ahead and tried the lower macro on a sheet with text that does not (shouldn't anyway) repeat. It seemed to work on that as well. I am attaching a sample file without repeats to see if you think the macro will work, all the way through, or if it would need to be adjusted.

Thank you for your effort, this is fantastic!

Much appreciated, Alec.

[rylo]

Hi

new file has a very different structure. Exactly what should the output look like? Or what 3 columns are you trying to use to create your matrix?

rylo

[pike]

Hi abuchan,
This should run in a few minutes
in stead of using b() to store the data it writes to the sheet2 and saves in it the dic1
dic1 and dic2 are then reference sheet2 cells

Please Login or Register  to view this content.

[abuchan]

Rylo,

I think I was too sleepy. I uploaded the file before massaging it. Here it is. I ran your Macro on it and it seems to work very nicely with text.

[abuchan]

Pike,

Wow. took less than two minutes! The results look amazing. It ended up with the same amount of unique parcel numbers but a different amount of columns which I will look at later this morning. Your work amazes me.

Thank you.

[abuchan]

I did some more testing and everything appears really good. It picked up about 10 other columns that were missed on earlier versions. I am going to process the data with it now and if all goes well, I will be able to close the thread. Nicely done, thank you.

[snb]

I'm curious about the speed of this one:

Please Login or Register  to view this content.

[abuchan]

I won't be able to look at it until this evening as my computer will be tied up until then. However, I supplied a link, several posts above, where you can download the actual file from me and run it. The data is exactly the same as I have here. There is just under 250,000 rows and you should end up with 62,651 unique parcel numbers. I would be interested to hear what you end up with. Otherwise, I will try it this evening as it is only 1:20 pm here.

Upon submitting my post, I noticed that you don't have 2007, sorry. I will run it tonight and give you a heads-up.

Thanks,
Alec.

[pike]

abuchan
cleaned up code

Please Login or Register  to view this content.

@snb mismatch error on

Please Login or Register  to view this content.

[snb]

I came up with a variation, that is less sensitive to large arrays (tested on 65536 rows).

Please Login or Register  to view this content.

[pike]

Hi snb,
after seven min mismatch on

Please Login or Register  to view this content.

[snb]

@Pike thanks for testing

It appears that match has a limit of 5461 in an array.
So I came up with something new (after which I intend to explore your dictionary-method)
The following code assumes column A to be sorted.
I used the 'real' data, but couldn't test more than 65536 rows.
The result in this system: 9,5 seconds

Please Login or Register  to view this content.

[pike]

the dic in 52 seconds but has more loops

You should free up some memory by using Option explicit and declaring arrays as variables ect.. these make all the difference and will make it quicker.

[snb]

@ Pike

The speed is 9,5 seconds (<20% of your result)
Option explicit nor declaring will improve that. You can't 'free' memory by using option explict, nor by declaring variables.

[pike]

Dude from Mrocrosoft - "Performance Advantage. Setting Option Explicit has the advantage of forcing type inference to be made at compile time instead of run time. This improves performance"

When declaring variables, VBA assigns particular amount
of memory to that variable depending on its data type.
Typically, 32bits for Integer values etc… But if you are
looking to allocate memory while run-time, that will reduce
program’s performance.

But well done for getting 5.2s with your code as I get an error on

Please Login or Register  to view this content.

I'll try sorting again
Anyway we dont want to scare abuchan with this banter ..

Yes dic are good as they can store arrays and retrevie change ect......

[snb]

Then you'd better put dots before cells:

.Columns(1).AdvancedFilter xlFilterCopy, , .Cells(1, 60), True

[pike]

Hi snb,
found the error and on this machine 124s

[snb]

On the oldest available system (Pentium II; 350Mhz) it took 70seconds.

You'd better check for viruses

[pike]

You are right about option explicit and no difference in speed due to the variable checking but you will free memory .
yep, i better check for bugs, How long did the dic code take to run on your machine?

[snb]

On the same machine it took 182 seconds; but the result isn't what it should be: there's only 1 column filled in sheet2.


I wonder why you didn't use a collection:

Please Login or Register  to view this content.

[pike]

Hi,
Many reasons; Speed of dic, Duplicate values will error and you cant see if it exists in the collection, how you use the array in collection if you cant retrieve its key and
Collection Item property is read-only.

[snb]

Please Login or Register  to view this content.

reduced writing operations
reduced number of loops

[abuchan]

"Scared", no. "Concerned", yes. While I enjoy your conversation and marvel at your 'magic', I used the last code provided by Pike and 'was' happy with the results. Does the conversation above suggest the results to not be accurate? Your help and guidance is much appreciated.

Thank you.

[pike]

Hi abuchan
No, more about the many different ways to do anything, Coding Practices, size limits with arrays and functions.
Its all good.

[abuchan]

Thank you for all your effort and work. Looks like magic to me!

[pike]

Jindon did all the hard work I just adapt his concepts