How to reorganize data from a table?

EduPAz

Board Regular
Joined
Mar 18, 2017
Messages
69
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi,

Can someone please help me with this?

I have this table


RevenueRevenueRevenue
ContractClientVendorType of ContractJan-20Feb-20Mar-20
A1YYJamesR1010010000
A2VVJamesD2020020000
B4ZZKevinD3030030000
B2ZZJamesR4040040000
A6WMikeR5050050000


Dynamic list for Vendor: James
Dynamic list for months: Feb-20




Only for Type of Contract R, how can I get a sorted table like this?


ClientRevenue
ZZ400
YY100



Thanks,

Edu
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Using power query here is the Mcode

Rich (BB code):
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Type of Contract] = "R") and ([Vendor] = "James")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Contract", "Vendor", "Type of Contract", "20-Jan", "20-Mar"})
in
    #"Removed Columns"

Book3
AB
1Client20-Feb
2YY100
3ZZ400
Sheet2
 
Upvote 0
Thank you for your answer!

Is there a way to do it with formulas? something like a mix of INDEX-MATCH-SUMIFS?

Thanks!

Edu
 
Upvote 0
Maybe. Will need to wait for a formula expert to provide input. I prefer to use Power Query in this scenario as it only involves three steps.
1. Filter on Type of Contract
2. Filter on Vendor
3. Remove columns not applicable to Feb-20
 
Upvote 0
A formula version:

Book1
ABCDEFGHIJKLMN
1RevenueRevenueRevenueVendorMonthContract TypeClientRevenue
2ContractClientVendorType of Contract20-Jan20-Feb20-MarJames20-FebRZZ400
3A1YYJamesR1010010000YY100
4A2VVJamesD2020020000  
5B4ZZKevinD3030030000  
6B2ZZJamesR4040040000
7A6WMikeR5050050000
8
Sheet14
Cell Formulas
RangeFormula
M2:M5M2=IF(N2="","",INDEX(B:B,AGGREGATE(15,6,ROW($B$3:$B$20)/($C$3:$C$20=$I$2)/($D$3:$D$20=$K$2)/(INDEX($E$3:$G$20,0,MATCH($J$2,$E$2:$G$2,0))=N2),COUNTIF(N$2:N2,N2))))
N2:N5N2=IFERROR(AGGREGATE(14,6,INDEX($E$3:$G$20,0,MATCH($J$2,$E$2:$G$2,0))/($C$3:$C$20=$I$2)/($D$3:$D$20=$K$2),ROWS(N$2:N2)),"")
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

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