Hi All,
I have two attached problem 1. where I have to find the result from two tables., 2. attached is the sheet.
could you please guide me how to solve it.
Thank you
Hi All,
I have two attached problem 1. where I have to find the result from two tables., 2. attached is the sheet.
could you please guide me how to solve it.
Thank you
Your worksheet is confusing.
Which 'tables' are your actual source data, which is your required outcome?
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
Ho Olly,
There are two problem,
1) I need result in F11 cell, Tables to be compare A to C column and E to F column.
2) ) I need result in K17 - M23 cells, Tables to be compare J to L columns
Hi
Use, for solve the first problem, in F11
Formula:Please Login or Register to view this content.
The data in J1:L12 - do you need to perform currency conversion on these values, too?
To solve the second problem
use in K17 and copy down and forward
Formula:Please Login or Register to view this content.
for your first issue, you can use Power Query to merge the two tables for the expected results. Here is the Mcode for that solution.
Please Login or Register to view this content.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Blending data from multiple tables, to generate flexible outputs is a perfect example of when to use PowerPivot.
In this answer, I've assumed you wish to apply currency conversion for the profit values in J:L.
Format your three source data tables as Tables:
Range A1:C6 = tblCountries
Range E1:F4 = tblFX
Range J1:L12 = tblProfits
Use PowerQuery to prepare each table, and Load To Data Model:
Query Countries:
Query FX:Please Login or Register to view this content.
Query Profits:Please Login or Register to view this content.
Please Login or Register to view this content.
With those three tables loaded to the Data Model, create relationships:
FX[Currency] - Countries[Currency]
Countries[Country] - Profits[Country]
Capture.PNG
Add a couple of MEASURES, to give the results you want:
Please Login or Register to view this content.
Finally, use Pivot Tables to present your results.Please Login or Register to view this content.
Note that as IND and SL do not appear in your first currency table, we can't identify which currency to use, so no value is returned in the 'Profit USD' measure, for these countries. To fix this, add those contries into the tblCountries table with their corresponding currency code, and ensure those currencies are listed in tblFX.
Attached workbook demonstrates all the above.
No Olly, in this case I don't require currency conversion. I need only output based on J1:L12.
Then just use a standard pivot table.
Last edited by AliGW; 07-12-2019 at 03:41 AM.
@José Augusto
Thank you Jose, It works, Have a query "What exact ,1 does in If error function end"
=VLOOKUP($E11,$A$2:$C$6,3,FALSE)*IFERROR(VLOOKUP(VLOOKUP($E11,$A$2:$C$6,2,FALSE),$E$2:$F$5,2,FALSE),1)
Hi @Merrysa
That value 1 is the conversion from USD to USD., i.e. to avoid IFERROR use
E5="USD"
F5=1
Got it, thanks a lot
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
@Olly
Very impressive, actually I never worked on PowerPivot , Understanding your method
Very impressive, actually I never worked on PowerPivot , Understanding your method
Last edited by AliGW; 07-12-2019 at 03:40 AM. Reason: Please don't quote unnecessarily!
Administrative Note:
Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!
For normal conversational replies, try using the QUICK REPLY box below.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks