Trying report data with multiple values linked to a cell with the same value.

H

hammerhead13

Board Regular
Joined
Aug 4, 2008
Messages
86
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. Mobile
Basically I am trying to figure out how to take a Cell "transaction #", find the most expensive item and then count all the items that are remaining from the transaction.

<TABLE cellSpacing=0 cols=3 rules=none border=0 frame=void><COLGROUP><COL width=86><COL width=86><COL width=86></COLGROUP><TBODY><TR><TD style="BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid" align=middle width=86 height=34>Example of Raw Data</TD><TD style="BORDER-TOP: #000000 1px solid" align=middle width=86>
</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid" align=middle width=86>
</TD></TR><TR><TD style="BORDER-LEFT: #000000 1px solid" align=middle height=34>Transaction #</TD><TD align=middle>Item</TD><TD style="BORDER-RIGHT: #000000 1px solid" align=middle>Price</TD></TR><TR><TD style="BORDER-LEFT: #000000 1px solid" align=middle height=18 SDNUM="1033;" SDVAL="100101">100101</TD><TD align=middle>Item 1</TD><TD style="BORDER-RIGHT: #000000 1px solid" align=middle SDNUM="1033;" SDVAL="199.99">199.99</TD></TR><TR><TD style="BORDER-LEFT: #000000 1px solid" align=middle height=18 SDNUM="1033;" SDVAL="100101">100101</TD><TD align=middle>Item 2</TD><TD style="BORDER-RIGHT: #000000 1px solid" align=middle SDNUM="1033;" SDVAL="4.99">4.99</TD></TR><TR><TD style="BORDER-LEFT: #000000 1px solid" align=middle height=18 SDNUM="1033;" SDVAL="100101">100101</TD><TD align=middle>Item 3</TD><TD style="BORDER-RIGHT: #000000 1px solid" align=middle SDNUM="1033;" SDVAL="20.99">20.99</TD></TR><TR><TD style="BORDER-LEFT: #000000 1px solid" align=middle height=18 SDNUM="1033;" SDVAL="100102">100102</TD><TD align=middle>Item 1</TD><TD style="BORDER-RIGHT: #000000 1px solid" align=middle SDNUM="1033;" SDVAL="300.99">300.99</TD></TR><TR><TD style="BORDER-LEFT: #000000 1px solid" align=middle height=18 SDNUM="1033;" SDVAL="100102">100102</TD><TD align=middle>Item 2</TD><TD style="BORDER-RIGHT: #000000 1px solid" align=middle SDNUM="1033;" SDVAL="45.99">45.99</TD></TR><TR><TD style="BORDER-LEFT: #000000 1px solid" align=middle height=18 SDNUM="1033;" SDVAL="100102">100102</TD><TD align=middle>Item 3</TD><TD style="BORDER-RIGHT: #000000 1px solid" align=middle SDNUM="1033;" SDVAL="42.99">42.99</TD></TR><TR><TD style="BORDER-LEFT: #000000 1px solid" align=middle height=18 SDNUM="1033;" SDVAL="100102">100102</TD><TD align=middle>Item 4</TD><TD style="BORDER-RIGHT: #000000 1px solid" align=middle SDNUM="1033;" SDVAL="35.99">35.99</TD></TR><TR><TD style="BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid" align=middle height=17>
</TD><TD style="BORDER-BOTTOM: #000000 1px solid" align=middle>
</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid" align=middle>
</TD></TR><TR><TD align=middle height=17>
</TD><TD align=middle>
</TD><TD align=middle>
</TD></TR><TR><TD style="BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid" align=middle height=50>Report Information Needed</TD><TD style="BORDER-TOP: #000000 1px solid" align=middle>
</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid" align=middle>
</TD></TR><TR><TD style="BORDER-LEFT: #000000 1px solid" align=middle height=66>Transaction</TD><TD align=middle>Primary Item Price (Highest)</TD><TD style="BORDER-RIGHT: #000000 1px solid" align=middle>Attached Item Total (everything else)</TD></TR><TR><TD style="BORDER-LEFT: #000000 1px solid" align=middle height=18 SDNUM="1033;" SDVAL="100101">100101</TD><TD align=middle SDNUM="1033;" SDVAL="199.99">199.99</TD><TD style="BORDER-RIGHT: #000000 1px solid" align=middle SDNUM="1033;" SDVAL="25.98">25.98</TD></TR><TR><TD style="BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid" align=middle height=18 SDNUM="1033;" SDVAL="100102">100102</TD><TD style="BORDER-BOTTOM: #000000 1px solid" align=middle SDNUM="1033;" SDVAL="300.99">300.99</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid" align=middle SDNUM="1033;" SDVAL="124.97">124.97</TD></TR></TBODY></TABLE>
 

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.
Sort by date Sort by votes
Only way I can think of is to use two pivot tables one for the Sum of alll items and the other is the Max of all items and then subtract out the max

Sum Pivot
Row Labels Sum of Price
100101 225.97
100102 425.96
Grand Total 651.93

Max Pivot
Row Labels Max of Price Everything Else
100101 199.99 25.98
100102 300.99 124.97
 
Upvote 0
What would the result look like if two (or more) items had the equal max value for a particular Transaction #?

Example:
Excel Workbook
ABC
1Transaction #ItemPrice
2100101Item 1199.99
3100101Item 24.99
4100101Item 3199.99
5100102Item 1300.99
6100102Item 245.99
7100102Item 342.99
8100102Item 435.99
Transaction Summary
 
Upvote 0
Ultimately I would only count it once and the other with the rest. Can this be done?
 
Upvote 0
hammerhead13 said:
Ultimately I would only count it once and the other with the rest. Can this be done?
Click to expand...
Depends a bit how often this may need to be done, how dynamic it needs to be etc. I'll outline a few ideas and if you need help to develop any that look like what you want post back and include more details of the help you need as well as which Excel version you are using.

I have assumed that your data is sorted by Transaction # per your sample data.

Idea 1
Introduce two new columns with these formulas, copied down.
Note that the D2 formula is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Now create a Pivot Table using columns A, D & E

Excel Workbook
ABCDEFGHI
1Transaction #ItemPricePrice MaxPrice OtherRow LabelsSum of Price MaxSum of Price Other
2100101Item 1199.99199.9925.98100101199.9925.98
3100101Item 24.99100102300.99124.97
4100101Item 320.99Grand Total500.98150.95
5100102Item 1300.99300.99124.97
6100102Item 245.99
7100102Item 342.99
8100102Item 435.99
9
Transaction Summary 1




Idea 2

Same extra columns as above but then use Advanced Filter with the criteria range as shown in F1:F2 to produce the results shown in columns G-->

Excel Workbook
ABCDEFGHIJK
1Transaction #ItemPricePrice MaxPrice OtherTransaction #ItemPricePrice MaxPrice Other
2100101Item 1199.99199.9925.98TRUE100101Item 1199.99199.9925.98
3100101Item 24.99100102Item 1300.99300.99124.97
4100101Item 320.99
5100102Item 1300.99300.99124.97
6100102Item 245.99
7100102Item 342.99
8100102Item 435.99
9
Transaction Summary 2



Idea 3

Same columns D:E as above but just use AutoFilter on say column D to show 'Non Blanks'.

Excel Workbook
ABCDE
1Transaction #ItemPricePrice MaxPrice Other
2100101Item 1199.99199.9925.98
5100102Item 1300.99300.99124.97
10
Transaction Summary 3
 
Upvote 0
If I want to Average the column of the other price but only count the cells the the primary is greater than 5.00, how would that look? Thanks Again!
 
Upvote 0
hammerhead13 said:
.. only count the cells the the primary is greater than 5.00 ..
Click to expand...
I'm not sure what you mean by that. Could you give a few small examples of data and expected output? Again could you also include an example where the higest price occurs more than once?
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
622
smozgur
smozgur
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
501
VBAEXCELNew
V
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
R
  • Question Question
Random
Replies
1
Views
580
Fluff
Fluff
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,584
Messages
6,179,693
Members
452,938
Latest member
babeneker

Share this page

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