Segregate a long client list into two

[atrialto]

Okay I will try to explain this the best I can, please let me know if you have any questions (I have attached a picture and example file for reference). I have an excel report I have to do every week. On the report, I have two types of clients (suppliers and restaurants) with a list of outstanding invoices for each client listed underneath the client name.

Now, what I need to do is segregate the report into two reports - one for each type of client.

So basically, I have a large list of clients, I need to seperate it into two different lists.

The problems I'm facing are:

1) There is very little on the excel sheet that distinguishes the two client types. One thing might be the invoice numbers. Most restaurants have 5 digit invoice numbers while suppliers have longer more complex numbers with letters and dashes on them.

2) Clients range from having anywhere from one outstanding invoice to fourty invoices. I'm not sure how I can do a selection like this in a range.

3) These lists are constantly updated with new clients/removal of old clients.

What I have been doing is going through the excel sheet and deleting clients one by one until I have only suppliers left. Then doing the same thing again until I only have restaurants left. This is very tedious and takes a very long time.

Note: The reason I have this issue is that I get the excel sheet as an export from the accounting system, and I have no way to seperate the client types before I export it due to a limitation in the software.

Thank you!

[davegugg]

I think you could throw a helper column in. Assuming helper column is in column B, you could put this in:
=IF(A5="Supplier","Supplier",IF(A5="Restaurant",Restaurant",IF(A5<>"","Filter",A4)))

Drag down to the bottom, and use the filter.

PS, it is much easier to help you if you post a workbook, not a picture of one.

[atrialto]

Welcome to the forum.

Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

Thanks.

Thanks and sorry!

Fixed and added a sample worksheet.

[Leith Ross]

Hello atrialto,

The following macro has been added to the attached workbook. A button has been added to run the macro for you. There are now two additional sheets named: "Restaurants" and "Suppliers". Each sheet contains the report information for each customer type.

ThisWorkbook Module COde

Please Login or Register  to view this content.

[atrialto]

Hello atrialto,

The following macro has been added to the attached workbook. A button has been added to run the macro for you. There are now two additional sheets named: "Restaurants" and "Suppliers". Each sheet contains the report information for each customer type.

]

Hi Leith, thanks so much for trying to help and taking the time to write that program.

Unfortunately, restaurants are listed as actual restaurant names, think (Applebees, McDonalds), and suppliers have actual names, so I cannot just sort it that way. The file I attached was just a short sample with generic data.

[Leith Ross]

Hello atrialto,

I may be able to pull something together but I will need to the actual data. Can you post a workbook with actual data you need sorted?

[atrialto]

Hello atrialto,

I may be able to pull something together but I will need to the actual data. Can you post a workbook with actual data you need sorted?

Hi Leith, thanks again.

I have attached a complete workbook with real data. Restaurants have invoices that are for example "75429". They are all 5 digits.

Thx

[Leith Ross]

Hello atrialto,

Thanks for posting the workbook. Since you mentioned there are exceptions to the rule for invoices, do you have some examples of this? Is there any logic to the assignment of the source numbers?

[atrialto]

Hello atrialto,

Thanks for posting the workbook. Since you mentioned there are exceptions to the rule for invoices, do you have some examples of this? Is there any logic to the assignment of the source numbers?

Well, I can tell you that all restaurants have 5 digit invoices with very few exceptions, if any. Suppliers have longer invoice numbers, except some of them all have bill back invoices, which can total 5 characters, but they always start with "BB". So a restaurant invoice would look like '74838', and a supplier invoice may lookl like 'BB123'.

I hope this helps.

[Leith Ross]

Hello atrialto,

That bit of information is big help. Thanks.

[Leith Ross]

Hello atrialto,

I updated the macro to separate the restaurants and suppliers by using the 5 digit invoice number. If the invoice is 5 digits with no alpha characters then it goes to the "Restaurants" sheet. Otherwise it goes to the "Suppliers" sheet.

No total lines are include on either sheet. I can easily change the macro to do so, if you need that information included.

Here is the revised macro code. This has been added to the attached workbook.

Please Login or Register  to view this content.

[atrialto]

Hey Leith. That really looks great. Thanks so much for your help. If it's easy to add the totals then yeah that would definitely be nice to have as well.

Can it be so the restaurants and suppliers worksheets are dynamically created, or do I have to create them myself every time?

Tx

[Leith Ross]

Hello atrialto,

The totals can be added at the bottom easily enough. The macro can add the sheets if they don't exist and if they it can clear them before transferring the data over. Will that work for you?

[atrialto]

Hello atrialto,

The totals can be added at the bottom easily enough. The macro can add the sheets if they don't exist and if they it can clear them before transferring the data over. Will that work for you?

For sure, that sounds great.