Sort data from one table into two automatically - No VBA

Camranjaber

New Member
Joined
Dec 17, 2015
Messages
5
Hi, I've looked thoroughly throughout forums and tutorials for a possible solution to the following situation, but to no avail. It may have to do with the wording I use in my search, as it's difficult to know how to ask this, but I'm just stumped.

I have a list of data, 50-200 rows a week, that comes in to my company that I need to sort into two tables. Obviously the goal is automation; there are many other divisions and processes in my company that a solution to this would also apply to. Need to do this without VBA.

In case context is useful, here is some background: the list is for purchases that our clients make that we reimburse them for. We use this data to calculate a piece of our COGS each month. You could say this is a food service industry, sort-of. The data is currently being pulled from Google sheets through a web import (Ribbon: Data -> Get External Data -> From Web) so it can be manipulated in Excel to be mapped to import into QuickBooks. The clients enter the data in Google sheets; this feeds to a sheet that pulls all facility data together, and I do the web import to Excel. I'm attempting to sort and filter this compilation data automatically so I can map it (in this case, mapping it refers to creating consistent syntax for facility names, applying classes and account numbers, etc.). I already know how to map it - it's fairly easy with a table or two and some RIGHT or MID and FIND functions, etc.

The issue is that my company is technically composed of two companies - one that is responsible for some clients, the other responsible for the rest (one actually has 19, the other has two for this particular branch of the company). So based on the name of the client, I'm trying to separate the name list automatically to have the two filtered on two different sheets. That is, I'll have the sheet that pulls the data from Google Sheets, then I'd like to have one tab (sheet) with formulas that creates a table ignoring the clients for the other company, then vice versa for the other company. I'll add that there are multiple entries, never consistent as to the quantity, for each client each week.

I guess I could just run some filters then copy and paste, but that's the way we are currently doing this, and I need to pass this on to some data entry employees who are notorious for messing that process up :S I feel like this may require some indirect formulas, row formulas, array formulas, and some creative algebra. I just can't wrap my brain around it at my level of skill - hopefully one day I'll be able to return the favor of assisting me as I improve!

So that was my question: how can I take data from a table and have it automatically sort into two different tables in two different places (preferably two different sheets)?

Secondary question: how can I get the other day that accompanies the client name to follow it? I imagine it is either the same process or easier.

Here is a made-up simulation of current data that has not been sorted:

Date EnteredVendorAmountClient Name
08/19/2016'Merica Food Sizzervice1,111.12Adamantine Cty
08/19/2016Burning Bakeries567.34Cobalt City
08/20/2016Pilky Pasturizing Plant7.77Molten County
08/21/2016Burning Bakeries500,000.00Molten County
08/21/2016'Merica Food Sizzervice999.00Cobalt City
08/22/2016Pilky Pasturizing Plant1,234.56Adamantine Cty
08/24/2016'Merica Food Sizzervice13,243.54Molten Cty

<tbody>
</tbody>

My goal would be to have something to this effect:
Table 1
Date EnteredVendorAmountClient Name
08/20/2016Pilky Pasturizing Plant7.77Molten Cty
08/21/2016Burning Bakeries500,000.00Molten Cty
08/24/2016'Merica Food Sizzervice13,243.54Molten Cty

<tbody>
</tbody>


Table 2:
Date EnteredVendorAmountClient Name
08/19/2016'Merica Food Sizzervice1,111.12Adamantine Cty
08/19/2016Burning Bakeries567.34Cobalt City

<tbody>
</tbody>
08/21/2016'Merica Food Sizzervice999.00Cobalt City
08/22/2016Pilky Pasturizing Plant1,234.56Adamantine Cty

<tbody>
</tbody>


Sorry if the tables are messy - they look fine to me but I know that's not always the case after submitting tables.

So that's my objective: I very much appreciate anyone that's taken the time to consider this, whether you post a reply or not. I hope, in addition to the satisfaction of helping a stranger in need, that this "problem" will tickle your brain, as well as give you a satisfaction that can only be achieved with solving math-related supposition.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Date EnteredVendorAmountClient Namehelper
08/19/2016'Merica Food Sizzervice1,111.12Adamantine Cty'Merica Food Sizzervice2
08/19/2016Burning Bakeries567.34Cobalt CityBurning Bakeries3
08/20/2016Pilky Pasturizing Plant7.77Molten CtyPilky Pasturizing Plant4
08/21/2016Burning Bakeries500,000.00Molten CtyBurning Bakeries5
08/21/2016'Merica Food Sizzervice999Cobalt City'Merica Food Sizzervice6
08/22/2016Pilky Pasturizing Plant1,234.56Adamantine CtyPilky Pasturizing Plant7
08/24/2016'Merica Food Sizzervice13,243.54Molten Cty'Merica Food Sizzervice8
Client NameMolten Cty
My goal would be to have something to this effect:
Table 1Count of helper
Date EnteredVendorAmountClient NamehelperTotal
08/20/2016Pilky Pasturizing Plant7.77Molten CtyBurning Bakeries51
08/21/2016Burning Bakeries500,000.00Molten Cty'Merica Food Sizzervice81
08/24/2016'Merica Food Sizzervice13,243.54Molten CtyPilky Pasturizing Plant41
Grand Total3
Table 2:
Date EnteredVendorAmountClient Name
08/19/2016'Merica Food Sizzervice1,111.12Adamantine CtyI used a helper to differentiate otherwise unique names
08/19/2016Burning Bakeries567.34Cobalt Citybut the offset returns the original names
08/21/2016'Merica Food Sizzervice999Cobalt Citythen using offset match we get this
08/22/2016Pilky Pasturizing Plant1,234.56Adamantine Cty
08/21/2016Burning Bakeries500000Molten Cty
08/24/2016'Merica Food Sizzervice13243.54Molten Cty
08/20/2016Pilky Pasturizing Plant7.77Molten Cty
assumption#N/A#N/A#N/A#N/A
we want to split ouy molten city#N/A#N/A#N/A#N/A
#N/A#N/A#N/A#N/A
test 1
spot that you entered molten county and the abbreviation molten cty ROFL
if the n/a's offend they can be trapped out with if iserror
a second identical set up allowing all client names
EXCEPT molten cty completes the task

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
This may help, but I should say right away, is there some reason you're unwilling to do this in VBA? It would be much, much faster and more accurate if you did. As far as formulas, assume you have 4 tabs named Original, Company 1, and Company 2. The Original tab contains your raw data, which would be the first table in your example. Assume the table starts in cell A1, which contains the Date Entered header. In cell E1, enter a header named Client Name (or whatever else you'd like to call it). In cell E2, enter the formula =IF(OR(D2="Molten Cty",D2="Molten County"),"Company 1","Company 2"). Drag this formula down all your data. This will tell you which of your 2 companies is associated with that particular client.

In the Company 1 tab, set up your headers as normal starting in cell A1. In cell A2, enter the formula =IF(Original!$E2="Company 1",Original!A2,1/0). Please note the mixed reference for the cell from the Original tab. It needs to be $E2 and not E2, $E$2, or E$2. Drag this formula to column D. Then select A2:D2 and drag the fill handle down as far as you need to go. This will set up your table and give you a #DIV/0! error where Company 2's data is. Then select all information in the table from cell A2 to whereever your last piece of data is in column D. Press F5 and click Special. Select the Formulas and make sure that only the Errors box has a check in it. Click OK. This will select all of the data in your table that has an error. Right click in that area and select the Delete option. Make sure that Shift cells up is selected and press OK.

Repeat this process for the Company 2 tab, using the formula =IF(Original!$E2="Company 2",Original!A2,1/0). You may need to format the cells in your 2 new tables the first time you do this to be what you need them to be. Once this is set up, when you put new data in your Original tab, just drag the formulas in the Company 1 and Company 2 tabs down as far as you need to go, then repeat the deleting errors process.

Without using VBA, this is the best I can come up with. If it's at all possible, you should consider doing this with VBA if you can.
 
Upvote 0

Forum statistics

Threads
1,212,928
Messages
6,110,734
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top