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:
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Date Entered[/TD]
[TD]Vendor[/TD]
[TD]Amount[/TD]
[TD]Client Name[/TD]
[/TR]
[TR]
[TD]08/19/2016[/TD]
[TD]'Merica Food Sizzervice[/TD]
[TD]1,111.12[/TD]
[TD]Adamantine Cty[/TD]
[/TR]
[TR]
[TD]08/19/2016[/TD]
[TD]Burning Bakeries[/TD]
[TD]567.34[/TD]
[TD]Cobalt City[/TD]
[/TR]
[TR]
[TD]08/20/2016[/TD]
[TD]Pilky Pasturizing Plant[/TD]
[TD]7.77[/TD]
[TD]Molten County[/TD]
[/TR]
[TR]
[TD]08/21/2016[/TD]
[TD]Burning Bakeries[/TD]
[TD]500,000.00[/TD]
[TD]Molten County[/TD]
[/TR]
[TR]
[TD]08/21/2016[/TD]
[TD]'Merica Food Sizzervice[/TD]
[TD]999.00[/TD]
[TD]Cobalt City[/TD]
[/TR]
[TR]
[TD]08/22/2016[/TD]
[TD]Pilky Pasturizing Plant[/TD]
[TD]1,234.56[/TD]
[TD]Adamantine Cty[/TD]
[/TR]
[TR]
[TD]08/24/2016[/TD]
[TD]'Merica Food Sizzervice[/TD]
[TD]13,243.54[/TD]
[TD]Molten Cty[/TD]
[/TR]
</tbody>[/TABLE]
My goal would be to have something to this effect:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Table 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date Entered[/TD]
[TD]Vendor[/TD]
[TD]Amount[/TD]
[TD]Client Name[/TD]
[/TR]
[TR]
[TD]08/20/2016[/TD]
[TD]Pilky Pasturizing Plant[/TD]
[TD]7.77[/TD]
[TD]Molten Cty[/TD]
[/TR]
[TR]
[TD]08/21/2016[/TD]
[TD]Burning Bakeries[/TD]
[TD]500,000.00[/TD]
[TD]Molten Cty[/TD]
[/TR]
[TR]
[TD]08/24/2016[/TD]
[TD]'Merica Food Sizzervice[/TD]
[TD]13,243.54[/TD]
[TD]Molten Cty[/TD]
[/TR]
</tbody>[/TABLE]
Table 2:[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Date Entered[/TD]
[TD]Vendor[/TD]
[TD]Amount[/TD]
[TD]Client Name[/TD]
[/TR]
[TR]
[TD]08/19/2016[/TD]
[TD]'Merica Food Sizzervice[/TD]
[TD]1,111.12[/TD]
[TD]Adamantine Cty[/TD]
[/TR]
[TR]
[TD]08/19/2016[/TD]
[TD]Burning Bakeries[/TD]
[TD]567.34[/TD]
[TD]Cobalt City[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]08/21/2016[/TD]
[TD]'Merica Food Sizzervice[/TD]
[TD]999.00[/TD]
[TD]Cobalt City[/TD]
[/TR]
[TR]
[TD]08/22/2016[/TD]
[TD]Pilky Pasturizing Plant[/TD]
[TD]1,234.56[/TD]
[TD]Adamantine Cty[/TD]
[/TR]
</tbody>[/TABLE]
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.
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:
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Date Entered[/TD]
[TD]Vendor[/TD]
[TD]Amount[/TD]
[TD]Client Name[/TD]
[/TR]
[TR]
[TD]08/19/2016[/TD]
[TD]'Merica Food Sizzervice[/TD]
[TD]1,111.12[/TD]
[TD]Adamantine Cty[/TD]
[/TR]
[TR]
[TD]08/19/2016[/TD]
[TD]Burning Bakeries[/TD]
[TD]567.34[/TD]
[TD]Cobalt City[/TD]
[/TR]
[TR]
[TD]08/20/2016[/TD]
[TD]Pilky Pasturizing Plant[/TD]
[TD]7.77[/TD]
[TD]Molten County[/TD]
[/TR]
[TR]
[TD]08/21/2016[/TD]
[TD]Burning Bakeries[/TD]
[TD]500,000.00[/TD]
[TD]Molten County[/TD]
[/TR]
[TR]
[TD]08/21/2016[/TD]
[TD]'Merica Food Sizzervice[/TD]
[TD]999.00[/TD]
[TD]Cobalt City[/TD]
[/TR]
[TR]
[TD]08/22/2016[/TD]
[TD]Pilky Pasturizing Plant[/TD]
[TD]1,234.56[/TD]
[TD]Adamantine Cty[/TD]
[/TR]
[TR]
[TD]08/24/2016[/TD]
[TD]'Merica Food Sizzervice[/TD]
[TD]13,243.54[/TD]
[TD]Molten Cty[/TD]
[/TR]
</tbody>[/TABLE]
My goal would be to have something to this effect:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Table 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date Entered[/TD]
[TD]Vendor[/TD]
[TD]Amount[/TD]
[TD]Client Name[/TD]
[/TR]
[TR]
[TD]08/20/2016[/TD]
[TD]Pilky Pasturizing Plant[/TD]
[TD]7.77[/TD]
[TD]Molten Cty[/TD]
[/TR]
[TR]
[TD]08/21/2016[/TD]
[TD]Burning Bakeries[/TD]
[TD]500,000.00[/TD]
[TD]Molten Cty[/TD]
[/TR]
[TR]
[TD]08/24/2016[/TD]
[TD]'Merica Food Sizzervice[/TD]
[TD]13,243.54[/TD]
[TD]Molten Cty[/TD]
[/TR]
</tbody>[/TABLE]
Table 2:[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Date Entered[/TD]
[TD]Vendor[/TD]
[TD]Amount[/TD]
[TD]Client Name[/TD]
[/TR]
[TR]
[TD]08/19/2016[/TD]
[TD]'Merica Food Sizzervice[/TD]
[TD]1,111.12[/TD]
[TD]Adamantine Cty[/TD]
[/TR]
[TR]
[TD]08/19/2016[/TD]
[TD]Burning Bakeries[/TD]
[TD]567.34[/TD]
[TD]Cobalt City[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]08/21/2016[/TD]
[TD]'Merica Food Sizzervice[/TD]
[TD]999.00[/TD]
[TD]Cobalt City[/TD]
[/TR]
[TR]
[TD]08/22/2016[/TD]
[TD]Pilky Pasturizing Plant[/TD]
[TD]1,234.56[/TD]
[TD]Adamantine Cty[/TD]
[/TR]
</tbody>[/TABLE]
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.
