Dear Gurus,
I looking for excel formula that needs to combine the Business and Country data file which needs to lookup the unique common ID for both Business and Country.
I have defined the ID with color code. Thanks in advance. The input sheet is just an example.
regards
Jaffir
With Power Query aka Get and Transform Data
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").
Please Login or Register to view this content.
It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.
- Follow this link to learn how to install Power Query in Excel 2010 / 2013.
- Follow this link for an introduction to Power Query functionality.
- Follow this link for a video which demonstrates how to use Power Query code provided.
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
Hi Alan, super i can see the output. Thanks. I will learn the power query for sure, but before quickly, how to load the two sources?
can you mention in simple steps?
thanks. I tried and failed.
Highlight each range and convert each to a table Ctl +T
then open Power Query to a blank query, click on Advanced Editor and paste my Mcode into the Editor. Read the links I have provided for more detailed instructions.
but your query is not adding the 'country' related values, means added only the country not their corresponding values
Reviewed your expected results. I mis interpreted your requirements. I am confused and need to understand on the criteria for joining the two tables. Please explain in simple terms the criteria for merging the two tables. When I join on the Metric ID. I end up with 1300 rows of data versus the 234 you have in your results.
In my attached file. the output having 234 rows that equals the input file. I donot see the rows exceeding i.e., 1300 rows.
I hope you can understand that the ID is common for Business and Country, I always want the ID in the first column so wherever the count for business or country is higher, the ID should be in last row. Hope this helps.
Note: I have 5 more tables same like business and Country.
Here is a formula-based proposal.
To match the business with the country in columns G:KFormula:
Please Login or Register to view this content.
The final output in columns R:Z is populated using reference formulas such as
In column R: =G2
In column Z: =P2
Note that the sequence skips the reference to column L.
Note that the formatting for the %age columns has to be changed from general to percentage.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Thats beautiful. Thanks JeteMC. Let me implement the formula for in actual sheet with dependent columns and know you for any hiccups.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks