Working with multiple, related text items

To pivot easily, you should normalize your data as far as possible - so each City should only ever appear in one column. Perhaps something like:

"

	A	B	C
1	Tour Name	City Number	City Name
2	Italy 2016	1	Rome
3	Italy 2016	2	Milan
4	Italy 2016	3	Florence
5	Italy 2017	1	Florence
6	Italy 2017	2	Pisa
7	Europe 2017	1	Rome
8	Europe 2017	2	Berlin
9	Europe 2017	3	Paris

"

Thank you. I'm having some trouble conceptualising the layout. Basically each tour has multiple fields with text variables and so many permutations of associated items across the raw data.
E.g.
Cities: Rome, Milan, Florence
Hotels: Hyatt, Sheraton, Hilton
Guide: Gary, Peter, Mary (more than one guide may be involved in a particular city on a tour)

A good principle though, is to normalize your data so that each field value only appears in one column. It sounds like you may need multiple tables, which you can then join and model, using power pivot. Perhaps something like:

"

Tour Name	Start Date	End Date
Italy 2016	01/04/2016	14/04/2016
Italy 2017	03/04/2017	17/04/2017
Europe 2017	12/07/2017	04/08/2017

"


"

Tour Name	Guide
Italy 2016	Franco
Italy 2017	Marco
Europe 2017	Franco
Europe 2017	Maria

"


"

Tour Name	City Number	City Name	Hotel Name
Italy 2016	1	Rome	Hilton
Italy 2016	2	Milan	Hilton
Italy 2016	3	Florence	Sheraton
Italy 2017	1	Florence	Hyatt
Italy 2017	2	Pisa	Sheraton
Europe 2017	1	Rome	Hilton
Europe 2017	2	Berlin	Hyatt
Europe 2017	3	Paris	Marriott

"



Might be easier to advise if you show an example of what you're working with, and what your expected output looks like.