LinkBack URL
About LinkBacks
I am wondering if it is possible to generate a third table from two tables which is the Cartesian product of the two. The attached Excel file illustrates my issue.
Thanks in adavance!
I am wondering if it is possible to generate a third table from two tables which is the Cartesian product of the two. The attached Excel file illustrates my issue.
Thanks in adavance!
Last edited by p3rlend; 04-19-2016 at 07:33 PM.
It can't be done, since the values are not in table 1 or table 2.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
There's a workbook at https://app.box.com/s/47b28f19d794b25511be that will do this:
Alice Jan Bob Feb Mar Alice Jan Alice Feb Alice Mar Bob Jan Bob Feb Bob Mar
Entia non sunt multiplicanda sine necessitate
First: I think it helps us help you if you define terms. I assume that this is what you mean by "Cartesian product" of two sets: https://en.wikipedia.org/wiki/Cartesian_product
If so, then the difficult part is generating the different combinations:
1,1
1,2
1,3
2,1
2,2
2,3
and so on.
I am not aware of any "built in" solution for generating these combinations. Most solutions for generating combinations like that that I see are based on "For..Next" loops in a symbolic programming language like VBA. You should be able to find any number of examples around the internet. Once you have your list of combinations generated in the spreadsheet, then, you can simply use the INDEX() function to return each element of each set. https://support.office.com/en-us/art...2-b56b061328bd
Please Login or Register to view this content.
Originally Posted by shg
Thanks for the replies.
VBA can be a solution, but I would prefer a more dynamic approach, if it exists.
I have a solution to take the Cartesian product, or cross join as it is also called, by using Power Query. Here http://exceleratorbi.com.au/cross-jo...h-power-query/ the approach is described. The two tables in my spreadsheet are added via Power Query. Table1 are duplicated, and one column are added to this new table. In the new custom column the formula =[Table1] is added. Then the custom column are expanded. This gives us a cross join (or Cartesian product) of Table1 and Table2.
The problem is to add a new input column in the spreadsheet to add more information to the new table (the cross join table). In the spreadsheet below, the Power Query tables are modelled, and one more column are added to the cross join table in the spreadsheet (not in the Power Query table). If we f.ex. add more months to Table2 in the spreadsheet and refresh the Power Query tables, the cross join table will update accordingly. But the information in the Hours column will not be consistent. The number that belongs to one person in a given month might change place to another person or another month.
So I am wondering if it is a solution to this problem. Is there a way to create a cross join table with Power Query, and then add a new column to this table such that people can input hours worked?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks