I have a list of items that I want to repeat multiple times, once for each location. Then I need to add the location to an adjacent column for each item. I'm looking for a macro or formula that would populate this automatically because right now it's a lot of copy paste and drag down.
So my list of items is on the Items tab in column A.
Column A |
---|
12456 |
13967 |
15786 |
Then location names are on the Stores tab in column A.
Column A |
---|
San Francisco |
Dallas |
New York |
On a new tab I want the item list in column B for each store in column A.
Column A | Column B |
---|---|
San Francisco | 12456 |
San Francisco | 13967 |
San Francisco | 15786 |
Dallas | 12456 |
Dallas | 13967 |
Dallas | 15786 |
New York | 12456 |
New York | 13967 |
New York | 15786 |
I've searched but haven't found any info on this specific problem.
First, I would use CTRL
+ T
to make both of these lists tables.
Then, in the Data tab of excel from table/range I'd load these both to power query.
Then, in the power query window under the Home tab I added a New Source from a blank query. I put the name of one table into the formula bar. Then Added a Custom Column and made that formula the name of the other table.
Expanding this added column will create the list of combinations.
Finally, close & load to your spreadsheet. Then you can refresh this output table and it will apply the logic to the tables if new information is added or deleted.