Hi guys, it's been a little while. But we have a new challenge.
Here's the breakdown:
I am compiling a list or more specifically a register of transactions. I have a log of data that is INPUT, and I have a separate log that is OUTPUT. I have both these logs, and want to reconcile them, as a FINAL output. The Input is blocks I purchased, whereas the Output is when those blocks are spent. However a third party application currently gives me my total, but I want to run my own validation.
Alot of the data uses Index and Match but for sake of brevity I am going to simplify.
I have 5 Columns as Outputs:
C1: Location, C2: Location Abbreviation, C3: Number of Blocks, C4: Block Price, C5: Block Owner.
*C5: Is multiple distinct owners.
The goal is to SUM and calculate the amount of Locations - Number of Blocks - Price - A Block Owner may own.
For Example.
A1: Florida, B1: FL, C1: 1, D1: $20, E1: Steve
A2: New York, B2: NYC, C2: 2, D2: $30, E2: Dave
A3: Virginia, B3: VA, C3: 1, D3: $15, E3: Steve
...
...
Going on for hundreds/thousands of lines.
I need to create formulas that sum all that data going down, and assigns to each owner his total.
Meaning:
Total Locations, Total number of Blocks per Location, Total Price of All Blocks, and Block Owner.
On the example above Steve owns two blocks in two different locations.
So I would need the output to give a summary of all locations Steve owns, and how many blocks of that location he owns.
Same thing for Dave.
I can see this done using Pivot tables, but ideally would like to automate as much as possible, avoiding Macros if possible.
So Steve's output would be:
Output Line 1 A1: Florida, B1: FL, C1: 1, D1: $20, E1: Steve
Output Line 2 A3: Virginia, B3: VA, C3: 1, D3: $15, E3: Steve
If he owned more "Florida" blocks then I would had added the cost and amount of blocks together for a total. So the formula would have to check if Florida exists twice, add them together etc.
Hopefully my explanations make sense.
Please let me know. I think the analogue to this is somewhat like Quicken's Register type of data input.
Thank you for the attention!
Ps. All this is just the INPUT Portion, Ill add the Output Challenges next
. Once we move forward with the input first.
Here's the breakdown:
I am compiling a list or more specifically a register of transactions. I have a log of data that is INPUT, and I have a separate log that is OUTPUT. I have both these logs, and want to reconcile them, as a FINAL output. The Input is blocks I purchased, whereas the Output is when those blocks are spent. However a third party application currently gives me my total, but I want to run my own validation.
Alot of the data uses Index and Match but for sake of brevity I am going to simplify.
I have 5 Columns as Outputs:
C1: Location, C2: Location Abbreviation, C3: Number of Blocks, C4: Block Price, C5: Block Owner.
*C5: Is multiple distinct owners.
The goal is to SUM and calculate the amount of Locations - Number of Blocks - Price - A Block Owner may own.
For Example.
A1: Florida, B1: FL, C1: 1, D1: $20, E1: Steve
A2: New York, B2: NYC, C2: 2, D2: $30, E2: Dave
A3: Virginia, B3: VA, C3: 1, D3: $15, E3: Steve
...
...
Going on for hundreds/thousands of lines.
I need to create formulas that sum all that data going down, and assigns to each owner his total.
Meaning:
Total Locations, Total number of Blocks per Location, Total Price of All Blocks, and Block Owner.
On the example above Steve owns two blocks in two different locations.
So I would need the output to give a summary of all locations Steve owns, and how many blocks of that location he owns.
Same thing for Dave.
I can see this done using Pivot tables, but ideally would like to automate as much as possible, avoiding Macros if possible.
So Steve's output would be:
Output Line 1 A1: Florida, B1: FL, C1: 1, D1: $20, E1: Steve
Output Line 2 A3: Virginia, B3: VA, C3: 1, D3: $15, E3: Steve
If he owned more "Florida" blocks then I would had added the cost and amount of blocks together for a total. So the formula would have to check if Florida exists twice, add them together etc.
Hopefully my explanations make sense.
Please let me know. I think the analogue to this is somewhat like Quicken's Register type of data input.
Thank you for the attention!
Ps. All this is just the INPUT Portion, Ill add the Output Challenges next
. Once we move forward with the input first.
Last edited:
