Showing all possible combinations of two columns in one.

[Tranquilo]

So I want to go through two columns, one with store numbers and the other with SKU's and create a third column that shows every possible combination of the two other columns.

EX:

Store # | SKU # | Concat |
------------------------------
ABC | 123 | ABC*123 |
DEF | 234 | ABC*234 |
| | DEF*123 |
| | DEF*234 |


Not a very good example mind you... But hopefully you get the idea (show all combinations of one store number with all the SKU numbers, then go to the next store number and do the same...)

So is there a quick and easy way to do this? So far the only easy way is to do a lot of copy and pasting and the quick way is to toss the whole report in the recycling bin.
Unfortunately, the reporting software we're using (dos based *shudder*) only takes these values to do a report.

Thanks in advance.

 

[SkipVought]

Hi,

You can use the PivotTable wizard to help you gen this list.

1) Copy the SKU #s to a new column, let's say column F

2) Copy then Store #s and the use Paste Special - TRANSPOSE to G1

3) Start the PivotTable Wizard and follow the instruction in NORMALIZE Your Table using the PivotTable Wizard faq68-5287.

Using your data I generated this list in about 15 seconds
[tt]
SKU Store
123 a
123 b
123 c
124 a
124 b
124 c
125 a
125 b
125 c
126 a
126 b
126 c
[/tt]
Then its just a matter of concatenating the values.

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]