Sort/transpose data based on one column

[ialana]

Hello. I need to put something like this:

Name	City	Country	Continent	Date	Count
A	Barcelona	Spain	Europe	01/01/2012	613
A	Barcelona	Spain	Europe	07/08/2012	981
A	Barcelona	Spain	Europe	05/03/2013	1205
A	Barcelona	Spain	Europe	21/04/2015	2309
B	London	UK	Europe	03/02/2015	1
B	London	UK	Europe	23/12/2016	3267
C	Kyoto	Japan	Asia	30/06/2014	984
C	Kyoto	Japan	Asia	02/01/2015	1267
C	Kyoto	Japan	Asia	07/07/2015	2346
C	Kyoto	Japan	Asia	23/03/2016	2687
C	Kyoto	Japan	Asia	21/02/2017	3002

<colgroup><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

into something like this:

Name

City

Country

Continent

Date 1

Count 1

Date 2

Count 2

Date 3

Count 3

Date 4

Count 4

Date 5

Count 5

A

Barcelona

Spain

Europe

01/01/2012

613

07/08/2012

981

05/03/2013

1205

21/04/2015

2309

B

London

UK

Europe

03/02/2015

1

23/12/2016

3267

C

Kyoto

Japan

Asia

30/06/2014

984

02/01/2015

1267

07/07/2015

2346

23/03/2016

2687

21/02/2017

3002

<colgroup><col><col><col><col><col span="10"></colgroup><tbody>
</tbody>

I know nothing about vba but have managed to copy/paste/get code working before.

Any help would be very much appreciated.

 

[Legacy 396030]

Hello,

This is a bit tricky but I think I have it. Use Column K as the input for the other table. Rearrange your columns as I have shown so the count formula works:

REF

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

1

City

Country

Continent

Name

Date

Count

City

Country

Continent

Name

Date 1

Date 2

Date 3

Date 4

Date 5

Count 1

Count 2

Count 3

Count 4

Count 5

2

Ba

Sp

Eu

A

01/01/2012

613

Ba

Sp

Eu

A

01/01/2012

08/07/2012

03/05/2013

21/04/2015

613

981

1205

2309

3

Ba

Sp

Eu

A

08/07/2012

981

Lo

UK

Eu

B

02/03/2015

23/12/2016

1

3267

4

Ba

Sp

Eu

A

03/05/2013

1205

C

5

Ba

Sp

Eu

A

21/04/2015

2309

D

6

Lo

UK

Eu

B

02/03/2015

1

E

7

Lo

UK

Eu

B

23/12/2016

3267

F

8

G

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula H2 {=IFERROR(INDEX(A$2:A$8,MATCH($K2,$D$2:$D$8,0)),"")} I2 {=IFERROR(INDEX(B$2:B$8,MATCH($K2,$D$2:$D$8,0)),"")} J2 {=IFERROR(INDEX(C$2:C$8,MATCH($K2,$D$2:$D$8,0)),"")} L2 {=IFERROR(INDEX($E$2:$E$8,SMALL(IF($D$2:$D$8=$K2,ROW($E$2:$E$8)-MIN(ROW($E$2:$E$8))+1),COLUMNS($K$2:K2))),"")} M2 {=IFERROR(INDEX($E$2:$E$8,SMALL(IF($D$2:$D$8=$K2,ROW($E$2:$E$8)-MIN(ROW($E$2:$E$8))+1),COLUMNS($K$2:L2))),"")} N2 {=IFERROR(INDEX($E$2:$E$8,SMALL(IF($D$2:$D$8=$K2,ROW($E$2:$E$8)-MIN(ROW($E$2:$E$8))+1),COLUMNS($K$2:M2))),"")} O2 {=IFERROR(INDEX($E$2:$E$8,SMALL(IF($D$2:$D$8=$K2,ROW($E$2:$E$8)-MIN(ROW($E$2:$E$8))+1),COLUMNS($K$2:N2))),"")} P2 {=IFERROR(INDEX($E$2:$E$8,SMALL(IF($D$2:$D$8=$K2,ROW($E$2:$E$8)-MIN(ROW($E$2:$E$8))+1),COLUMNS($K$2:O2))),"")} Q2 {=IFERROR(INDEX($F$2:$F$8,SMALL(IF($D$2:$D$8=$K2,ROW($F$2:$F$8)-MIN(ROW($F$2:$F$8))+1),COLUMNS($K$2:K2))),"")} R2 {=IFERROR(INDEX($F$2:$F$8,SMALL(IF($D$2:$D$8=$K2,ROW($F$2:$F$8)-MIN(ROW($F$2:$F$8))+1),COLUMNS($K$2:L2))),"")} S2 {=IFERROR(INDEX($F$2:$F$8,SMALL(IF($D$2:$D$8=$K2,ROW($F$2:$F$8)-MIN(ROW($F$2:$F$8))+1),COLUMNS($K$2:M2))),"")} T2 {=IFERROR(INDEX($F$2:$F$8,SMALL(IF($D$2:$D$8=$K2,ROW($F$2:$F$8)-MIN(ROW($F$2:$F$8))+1),COLUMNS($K$2:N2))),"")} U2 {=IFERROR(INDEX($F$2:$F$8,SMALL(IF($D$2:$D$8=$K2,ROW($F$2:$F$8)-MIN(ROW($F$2:$F$8))+1),COLUMNS($K$2:O2))),"")} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Legacy 396030]

Also Column K should be the only column to enter data in the other table. If there aren't any entries in this column then Column L will have 00/01/1900 and Column Q will have "0". Maybe someone can alter the formulas to take care of that issue.

 

[ialana]

Thank you very much, BRS!

I've rearranged my table like you did, but unfortunately I'm only getting blank cells everywhere. Any ideas why?

 

[Legacy 396030]

After you put the formula in Date 1 cell L2 in my example you need to press CTRL+SHIFT+ENTER to make it an array. Then drag across to the Date 5 cell then drag it down to bottom of range. Do the same for the count

 

[Legacy 396030]

Also do the same for cell H2 and drag to J2 then down. The curly brackets at the beginning and end of the formulas mean they are arrays. Column K is the only column to enter the data. The rest will fill in from your data table.

 

[ialana]

Still blank I'm afraid. This is what it looks like:

 

[Legacy 396030]

Put the letter "A" in cell K2

 

[ialana]

Bingo! I'm going to work with that now. Thanks again!

 

[Legacy 396030]

You are very welcome! Let me know if you need anything else.