Adjusting the layout of disparate data in excel

[Atlas87]

Not sure if that title is the snappiest way to label this query.

I have results from a questionnaire which are partly about jurisdictions of interest of businesses. As shown (poorly) below, they way they have been pulled in to excel makes it quite difficult to interact with them. The businesses appear in column one as the first entry in a row and then the rest of the row are the jurisdictions. I am especially interested in using the 3d maps feature here.

Is there a way/what is the best way to get this awkward layout of data into a place where i can interact with it more easily (pivot tables, 3d maps etc.) ???.

I tried using the go to BLANKS and deleting them but this messed with the data layout and attributed some answers to business who didnt give that answer etc.

There are too many rows to individually drag into the layer sections of 3d maps. I want 3d maps to recognise that i have a bunch of business who have each given a list of jurisdictions and be able to show this on the map and heatmap those which were mentioned most often.

Any light to be shed???

Thanks in advance!! [TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]spain[/TD]
[TD]greece[/TD]
[TD]iran[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]america[/TD]
[TD]india[/TD]
[TD]afghan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]france[/TD]
[TD]italy[/TD]
[TD]czech rep[/TD]
[TD]spain[/TD]
[TD]india[/TD]
[TD][/TD]
[TD]UK[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Col Delane]

In your example:
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]spain[/TD]
[TD]greece[/TD]
[TD]iran[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]america[/TD]
[TD]india[/TD]
[TD]afghan[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]france[/TD]
[TD]italy[/TD]
[TD]czech rep[/TD]
[TD]spain[/TD]
[TD]india[/TD]
[TD]UK[/TD]
[/TR]
</tbody>[/TABLE]

1. Are Spain in row 1, and France in row 3 the "businesses" and the other countries in whatever cell the "jurisdictions"?
2. If so, is an example of the problem you're trying to overcome the split of the jurisdictions for Spain over rows 1 & 2?
3. How many possible jurisdictions are there per business (i.e. what's the max. No. of columns that would contain a value for each business)?

If the answers to 1 & 2 = Yes, and 3 = 5 (as per the data shown for France), try this formula-based solution to create a second "clean" table of your data that can be sorted to group the "-" businesses together, and then pivotted:

Spreadsheet
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]J[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]K[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]L[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]M[/COLOR]​

[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​

[/TD]
[TD]

Data​

[/TD]
[TD]

Data​

[/TD]
[TD]

Data​

[/TD]
[TD]

Data​

[/TD]
[TD]

Data​

[/TD]
[TD]

Data​

[/TD]
[TD][/TD]
[TD]

Formula​

[/TD]
[TD]

Formula​

[/TD]
[TD]

Formula​

[/TD]
[TD]

Formula​

[/TD]
[TD]

Formula​

[/TD]
[TD]

Formula​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​

[/TD]
[TD]

Business​

[/TD]
[TD]

Juris. 1​

[/TD]
[TD]

Juris. 2​

[/TD]
[TD]

Juris. 3​

[/TD]
[TD]

Juris. 4​

[/TD]
[TD]

Juris. 5​

[/TD]
[TD][/TD]
[TD]

Business​

[/TD]
[TD]

Juris. 1​

[/TD]
[TD]

Juris. 2​

[/TD]
[TD]

Juris. 3​

[/TD]
[TD]

Juris. 4​

[/TD]
[TD]

Juris. 5​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​

[/TD]
[TD]

spain​

[/TD]
[TD]

greece​

[/TD]
[TD]

iran​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

spain​

[/TD]
[TD]

greece​

[/TD]
[TD]

iran​

[/TD]
[TD]

america​

[/TD]
[TD]

india​

[/TD]
[TD]

afghan​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​

[/TD]
[TD][/TD]
[TD]

america​

[/TD]
[TD]

india​

[/TD]
[TD]

afghan​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

-​

[/TD]
[TD]

-​

[/TD]
[TD]

-​

[/TD]
[TD]

-​

[/TD]
[TD]

-​

[/TD]
[TD]

-​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​

[/TD]
[TD]

france​

[/TD]
[TD]

italy​

[/TD]
[TD]

czech rep​

[/TD]
[TD]

spain​

[/TD]
[TD]

india​

[/TD]
[TD]

UK​

[/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

france​

[/TD]
[TD]

italy​

[/TD]
[TD]

czech rep​

[/TD]
[TD]

spain​

[/TD]
[TD]

india​

[/TD]
[TD]

UK​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​

[/TD]
[TD]

america​

[/TD]
[TD]

spain​

[/TD]
[TD]

greece​

[/TD]
[TD]

iran​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

america​

[/TD]
[TD]

spain​

[/TD]
[TD]

greece​

[/TD]
[TD]

iran​

[/TD]
[TD]

UK​

[/TD]
[TD]

india​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​

[/TD]
[TD][/TD]
[TD]

UK​

[/TD]
[TD]

india​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

-​

[/TD]
[TD]

-​

[/TD]
[TD]

-​

[/TD]
[TD]

-​

[/TD]
[TD]

-​

[/TD]
[TD]

-​

[/TD]
[/TR]
</tbody>[/TABLE]

Formulas
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]J[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]K[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]L[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]M[/COLOR]​

[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​

[/TD]
[TD]

Formula​

[/TD]
[TD]

Formula​

[/TD]
[TD]

Formula​

[/TD]
[TD]

Formula​

[/TD]
[TD]

Formula​

[/TD]
[TD]

Formula​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​

[/TD]
[TD]

Business​

[/TD]
[TD]

Juris. 1​

[/TD]
[TD]

Juris. 2​

[/TD]
[TD]

Juris. 3​

[/TD]
[TD]

Juris. 4​

[/TD]
[TD]

Juris. 5​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​

[/TD]
[TD="bgcolor: #B8CCE4"]

= IF( NOT( ISBLANK( $A3 )), $A3, "-" )​

[/TD]
[TD]

= IF( AND( $A3 <> "", B3 <> "" ), B3, IF( $A4 = "", IFERROR( INDEX( $B4:$F4, 1, COUNTIFS( $B3:B3, "") ), "-" ), "-" ) )​

[/TD]
[TD]

= IF( AND( $A3 <> "", C3 <> "" ), C3, IF( $A4 = "", IFERROR( INDEX( $B4:$F4, 1, COUNTIFS( $B3:C3, "") ), "-" ), "-" ) )​

[/TD]
[TD]

= IF( AND( $A3 <> "", D3 <> "" ), D3, IF( $A4 = "", IFERROR( INDEX( $B4:$F4, 1, COUNTIFS( $B3:D3, "") ), "-" ), "-" ) )​

[/TD]
[TD]

= IF( AND( $A3 <> "", E3 <> "" ), E3, IF( $A4 = "", IFERROR( INDEX( $B4:$F4, 1, COUNTIFS( $B3:E3, "") ), "-" ), "-" ) )​

[/TD]
[TD]

= IF( AND( $A3 <> "", F3 <> "" ), F3, IF( $A4 = "", IFERROR( INDEX( $B4:$F4, 1, COUNTIFS( $B3:F3, "") ), "-" ), "-" ) )​

[/TD]
[/TR]
</tbody>[/TABLE]