Forum Discussion
Preparation of .csv file with multiple unique IDs
I have two files - one for person and one for place. The Person file contains 45 Person IDs and the Place file contains 85 Place IDs. I want to be able to produce a single .csv file where each Person ID is assigned to each Place ID but cannot find an easy way of doing this.
I have shown an example below with a smaller amount of unique IDs:
| Person ID |
| 0000000001AD1 |
| 0000000001AD2 |
| 0000000001AD3 |
| Place ID |
| 0000000001ZZ1 |
| 0000000001ZZ2 |
| 0000000001ZZ3 |
The final file would therefore look like this:
| Person ID | Place ID |
| 0000000001AD1 | 0000000001ZZ1 |
| 0000000001AD2 | 0000000001ZZ2 |
| 0000000001AD3 | 0000000001ZZ3 |
| 0000000001AD1 | 0000000001ZZ1 |
| 0000000001AD2 | 0000000001ZZ2 |
| 0000000001AD3 | 0000000001ZZ3 |
| 0000000001AD1 | 0000000001ZZ1 |
| 0000000001AD2 | 0000000001ZZ2 |
| 0000000001AD3 | 0000000001ZZ3 |
Is there a simple way of doing this rather then copying and pasting as I need to end up with 3,825 rows of data to allow for each unique combination?
Thanks
Hi Karen,
If data is structured like this
you may use in F3
=OFFSET($B$2,INT((ROW()-ROW($D$2)-1)/COUNTA($D$3:$D$500))+1,0)
in G3
=OFFSET($D$2,MOD(ROW()-ROW($D$2)-1,COUNTA($D$3:$D$500))+1,0)
and drag that cells down till combinations end.
Same is in attached.
3 Replies
Hi Karen,
If data is structured like this
you may use in F3
=OFFSET($B$2,INT((ROW()-ROW($D$2)-1)/COUNTA($D$3:$D$500))+1,0)
in G3
=OFFSET($D$2,MOD(ROW()-ROW($D$2)-1,COUNTA($D$3:$D$500))+1,0)
and drag that cells down till combinations end.
Same is in attached.
This is great - thanks very much!
You are welcome
