Tricky Sorting Challenge

[jclaim]

Greetings,

I am attempting to implement a very challenging sort, and I have no idea how to do it. The spreadsheet lists a series of retail sales and the taxes collected on the respective sales. I need to sort the sales by state, and then county, and then city. The problem is that the state, county, city, and district information for each sale is listed in different rows. So for every sale, you have one row that shows most of the information for that sale, including the state the sale was made to, and then you have a separate row for the county of the sale, another for the city, and another for the district. I have attached the spreadsheet for reference. I would greatly appreciate any help.

Thank you,

Jarvis

[jaslake]

Hi jclaim

Attached shows one way to do it...run Sub Fill_Blanks()

[FDibbins]

Hi and welcome to the forum

here is a formula alternative. Enter into M2, and copy across to P2, and copy down....

=IF($A2="",M1,OFFSET($F1,MOD(ROW(),4)-2+COLUMN(A1),0))

[jclaim]

John,

Thank you, but the worksheet you sent me is not sorted? Am I missing something?

jarvis

[jclaim]

John,

Also, how do I run Sub Fill_Blanks()?

Thanks.

[jaslake]

Hi jclaim

I'll put a Button on the Worksheet from which to run it...

[jclaim]

Thanks John!

[jaslake]

Hi jclaim

It's attached in my Post #6...

[jclaim]

John,

This is certainly a start! But is it possible to have all the information together on one worksheet?

[jaslake]

Hi jclaim

Possibly...can you show me what you have in mind?

[jclaim]

John,

What I would like ideally is for all the data for each respective transaction to be contained on its own row. Does that make sense?

Jarvis

[jclaim]

John,

Here is a new version of the worksheet that contains some more columns for the transactions.

jarvis

[jclaim]

John,

I have to leave for the rest of the night, but I will pick back up with you tomorrow. Thanks SO MUCH for your help.

Jarvis

[jclaim]

FDibbins,

Thank you for your help too. I have not yet looked at your solution, but I will tomorrow.

Jarvis

[jaslake]

Hi Jarvis

I need to see what you're expected output looks like (not the entire file you attached...an abbreviated version). I don't picture it. Give me a before and after version.

[jclaim]

John,

First of all, thanks again for your help with this. I need someone with your level of expertise.

I have attached a workbook that contains a "start" sheet and an "end" sheet. The "end" sheet shows what I am hoping you can convert the start sheet into (though I only show one sale as an example in the end sheet). I have filled in the columns that are different from the original, "start" sheet. Basically, I would like to take the information that is broken down into 4 total rows per sale on the start sheet and convert it into a column-ed organization, so that each sale will only comprise one row, instead of 4 rows. That way I can sort the rows as I wish. Please let me know if you need any further clarification. And thank you so much again for your help!

Jarvis

[jaslake]

Hi Jarvis

This Code in in the attached...let me know of issues.

Please Login or Register  to view this content.

[jclaim]

John,

I believe YOU DID IT! You are incredible! Thank you so much. Let me continue to review the "output" and check for any issues. Also, how do I go about putting this code in for other worksheets? You see, I will have new worksheets every month that I will need to convert to the "output" format. How do I do that myself (so I don't have to bother you every time!). Thanks, again, John for all your kind help. I appreciate it more than you know.

jarvis

[jaslake]

Hi Jarvis

Do you store all these Monthly Workbooks in the SAME Folder? What's the FULL Path of that Folder?

How many Worksheets are there in the Monthly Workbook? What's the NAME of the Source Worksheet we're dealing with? In your Sample File it was called "Beginning Sheet"; what is it in the "Real World"? Is the Source Worksheet Name ALWAYS the SAME?

[jclaim]

John,

I sent you a private message last night.

Jarvis

[jaslake]

Hi Jarvis

Received and answered.

[jclaim]

Hi John,

I just realized that the actual monthly worksheet will have a few more columns. So in case that affects the code, let me upload an example of one of the actual monthly worksheets that contains all the columns.

[jaslake]

Hi Jarvis

Yes, that does affect the Code...

[jaslake]

When you post this new version be certain to show the "After" of a few records...

[jclaim]

John,

Attached is the new version of the workbook containing a sample "input" worksheet, as well as an "output" worksheet that contains a few examples of what I hope the rows will look like in the output. Please note well that I have added three additional sets of columns for sales to addresses that have multiple "district" jurisdictions. By far the majority of sales will only have one district jurisdiction, but the number of jurisdictions can be up to four. I have provided an output example of a sale that has two district jurisdictions, which hopefully will provide you enough of a "picture" of what I am describing here. As always, thanks for your help and just let me know if you need any further clarification on anything.

Jarvis

[jaslake]

Hi Jarvis

This complicates things dramatically...not sure I can pull it off. I'll get back to you...

By far the majority of sales will only have one district jurisdiction, but the number of jurisdictions can be up to four

[jclaim]

John,

I understand if you are not able. If there is anything I can do to help, let me know.

Jarvis

[jaslake]

Hi Jarvis

Place the attached workbook and a COPY of your Data File in the SAME Folder. Open the attached workbook, click the Button.

The Code will ask you what Data File you wish to open. Select the file, select Open... and let 'er run.

Let me know of issues.

[jclaim]

John,

I'm away from my computer now, but I will test it out tonight and let you know how it goes. I'm excited to find out! Thanks a million for your help.

Jarvis

[jclaim]

John,

Are you kidding me?! This is outstanding. I'm amazed at your abilities. And I am so grateful for your help.

So far I have not found any issues. I have run the macro on the sheet you worked with, as well as on another sheet covering a different time period. And both sheets appear to have generated the desired output without a hitch. I will give everything a more thorough testing and let you know if any issues arise.

Thanks again for everything!

Sincerely,
Jarvis

[jaslake]

Hi Jarvis

You're welcome...glad I could help.

If this resolves your issue please mark your Thread as SOLVED.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[jclaim]

John,

I just marked the thread as "solved." I will let you know if I come upon any issues in the macro, but so far everything looks good.

Thanks for everything,

Jarvis

[jclaim]

John,

Hope all is well. Today I ran the macro on my April version of the worksheet, and unfortunately the macro is not outputting the district information correctly. I have spent the past hour diagnosing the issue the best I can from my end, because I didn't want to waste your time. And I think I figured out the problem! I discovered the output information was correct for about the first half or so of orders, and when I checked the input sheet at the point in the orders where the output sheet begins to be incorrect, lo and behold, I found an order with 5 districts! You will recall that I told you there can be up to 4 districts, and I know you created the macro according to those parameters. Well apparently I was wrong. I did not have enough sales history to know, but I should have assumed it could be more than I had seen to that point, which was 4 districts. So, if you would be so kind, I would GREATLY appreciate it if you could tweak the macro to account for up to, let's say, 6 districts, to be safe. Please let me know if this will be possible. Again, I am sorry for not giving you the correct number of districts in the first place. Thanks, John.

Jarvis

[jaslake]

Hi Jarvis

I'll look at this later this evening.

[jclaim]

Thanks, John. You're the best!
Jarvis

[jaslake]

Hi Jarvis

I tested with mock data...you'll need to test it with a COPY of your real data.

[jclaim]

John,

I just checked out the new macro, and . . . it is almost perfect! The district information is still a little off, but apparently it is limited to the new district columns generated (districts 4 and 5). I tried to attach a workbook that contains the real data I am using after I have run the macro on it and have sorted it by state jurisdiction name, which sorting makes it easier to see the remaining issues. But the file size was too big. So I've simply attached a copy of the real data, so you will have to run the macro on and then sort it by the first "jurisdiction name," which is the state jurisdiction name. Also, note that only orders in three states are candidates for any district information, namely, orders in the states of California, Kentucky, and Texas--but primarily California and Texas. This knowledge makes it easier to see the issues. So what are the issues? Well, if you will look at the output sheet generated by the macro and sorted according to state jurisdiction name, you will see that all looks to be fine up to the first four districts (which was the extent of the original macro). But the last two districts (4 and 5) are problematic. Sometimes this area will be entirely blank, and other times district information will appear in these columns with orders that the information does not belong to. You can see this easily by noting that only orders to California, Texas, and Kentucky should have any district information whatsoever. Yet, district information will appear in these columns for orders to other states. I trust you will be able to see the issues I am describing, but if you need any further clarification, please let me know. As always, a big thank you!!

Jarvis

[jaslake]

Hi Jarvis

I thought that last effort went a bit too smooth...try this...

[jclaim]

John,
At first glance, it looks like you've worked your magic once again!! I'll get it a more thorough perusal and let you know if I find any issues. If not, I will mark the thread "solved." I can't thank you enough for your kind help. I'd be up a creek without you!
All the best,
Jarvis

[jclaim]

Hi John,

I hope life has treated you well these past two and a half years since you helped me with my "tricky sorting challenge." I am writing in hopes that you might be so kind to make a tweak to the macro.

If you review our thread, you will see that the final change you made was to add a couple of more "district" columns. You created the macro to accommodate up to six districts. I thought that would be enough. But lo and behold, it looks like some sales are being made to areas with 7 districts! So, I was hoping you could update the macro to output 8 district columns (8 just to be safe).

Please let me know if this is possible. Thanks so much for your help in the past and any help you can afford now.

Jarvis