Convert Data

[sunrise06]

I have uploaded to my Google Drive an Excel spreadsheet - https://drive.google.com/file/d/1JzPo1MSrQO44SOf1SBR6oRdPs34icPp2/view?usp=sharing

There are two tabs, ORIG and DESIRED RESULTS. This is just a subset of a much longer file.

The goal is to have all lines with the same address on one line.

Any ideas how to accomplish this?

Thanks

 

[sunrise06]

That link might not work, here is another one- https://docs.google.com/spreadsheets/d/1ckp_jj8JNQQfTyTo0i5faa5j2LET_hej1L7dWb76SyI/edit?usp=sharing

 

[sandy666]

are you able to use PowerQuery (Get&Transform) ?

 

[sunrise06]

Not sure, not very strong with Power Query. With some lines with the same address might be just one line yet another time it could be 4 or 5. Would Power Query do that?

 

[sandy666]

look at this
whether it is acceptable?

ID.1

Distance.1

Last Name.1

First Name.1

Level.1

Mail Street Number1.1

Mail Street Number2.1

Mail City.1

Mail State.1

Mail Zip Code.1

Building.1

ID.2

Distance.2

Last Name.2

First Name.2

Level.2

Mail Street Number1.2

Mail Street Number2.2

Mail City.2

Mail State.2

Mail Zip Code.2

Building.2

ID.3

Distance.3

Last Name.3

First Name.3

Level.3

Mail Street Number1.3

Mail City.3

Mail State.3

Mail Zip Code.3

Building.3

ID.4

Distance.4

Last Name.4

First Name.4

Level.4

Mail Street Number1.4

Mail City.4

Mail State.4

Mail Zip Code.4

Building.4

ID.5

Distance.5

Last Name.5

First Name.5

Level.5

Mail Street Number1.5

Mail City.5

Mail State.5

Mail Zip Code.5

Building.5

ID.6

Distance.6

Last Name.6

First Name.6

Level.6

Mail Street Number1.6

Mail City.6

Mail State.6

Mail Zip Code.6

Building.6

ID.7

Distance.7

Last Name.7

First Name.7

Level.7

Mail Street Number1.7

Mail City.7

Mail State.7

Mail Zip Code.7

Building.7

Distance.8

1​

0​

LN1

FN1

07

Addr1

City

State

ZIP

R

72​

2​

0​

LN2

FN2

01

Addr2

City

State

ZIP

K

3​

1110​

LN2

FN3

3​

Addr2

City

State

ZIP

K

12​

1110​

LN10

FN15

A

Addr10

City

State

ZIP

K

156​

4​

0​

LN3

FN4

05

Addr3

2Addr3

City

State

ZIP

D

7​

1140​

LN5

FN10

2​

Addr5

2Addr7

City

State

ZIP

D

9​

120​

LN7

FN12

05

Addr7

City

State

ZIP

D

11​

1320​

LN9

FN14

2​

Addr9

City

State

ZIP

D

15​

1510​

LN12

FN19

1​

Addr12

City

State

ZIP

D

16​

1580​

LN13

FN20

4​

Addr13

City

State

ZIP

D

17​

1580​

LN14

FN21

4​

Addr14

City

State

ZIP

D

158​

5​

0​

LN4

FN5

07

Addr4

City

State

ZIP

H

8​

1190​

LN6

FN11

8​

Addr6

City

State

ZIP

H

10​

130​

LN8

FN13

08

Addr8

City

State

ZIP

H

132​

6​

0​

LN4

FN6

03

Addr4

City

State

ZIP

O

6​

1190​

LN4

FN7

3​

Addr4

City

State

ZIP

O

6​

1190​

LN4

FN8

03

Addr4

City

State

ZIP

O

6​

1190​

LN4

FN9

3​

Addr4

City

State

ZIP

O

119​

13​

0​

LN11

FN16

01

Addr11

City

State

ZIP

P

14​

1560​

LN11

FN17

2​

Addr11

City

State

ZIP

P

14​

1560​

LN11

FN18

02

Addr11

City

State

ZIP

P

18​

1560​

LN15

FN22

4​

Addr15

City

State

ZIP

P

162​

19​

0​

LN16

FN23

A

Addr16

City

State

ZIP

B

164​

 

[sandy666]

here is a proper table
I made mistake dot vs comma - above

ID.1

Distance.1

Last Name.1

First Name.1

Level.1

Mail Street Number1.1

Mail Street Number2.1

Mail City.1

Mail State.1

Mail Zip Code.1

Building.1

ID.2

Distance.2

Last Name.2

First Name.2

Level.2

Mail Street Number1.2

Mail Street Number2.2

Mail City.2

Mail State.2

Mail Zip Code.2

Building.2

ID.3

Distance.3

Last Name.3

First Name.3

Level.3

Mail Street Number1.3

Mail City.3

Mail State.3

Mail Zip Code.3

Building.3

ID.4

Distance.4

Last Name.4

First Name.4

Level.4

Mail Street Number1.4

Mail City.4

Mail State.4

Mail Zip Code.4

Building.4

ID.5

Distance.5

Last Name.5

First Name.5

Level.5

Mail Street Number1.5

Mail City.5

Mail State.5

Mail Zip Code.5

Building.5

ID.6

Distance.6

Last Name.6

First Name.6

Level.6

Mail Street Number1.6

Mail City.6

Mail State.6

Mail Zip Code.6

Building.6

ID.7

Distance.7

Last Name.7

First Name.7

Level.7

Mail Street Number1.7

Mail City.7

Mail State.7

Mail Zip Code.7

Building.7

1​

0.072​

LN1

FN1

07

Addr1

City

State

ZIP

R

2​

0.111​

LN2

FN2

01

Addr2

City

State

ZIP

K

3​

0.111​

LN2

FN3

3​

Addr2

City

State

ZIP

K

12​

0.156​

LN10

FN15

A

Addr10

City

State

ZIP

K

4​

0.114​

LN3

FN4

05

Addr3

2Addr3

City

State

ZIP

D

7​

0.12​

LN5

FN10

2​

Addr5

2Addr7

City

State

ZIP

D

9​

0.132​

LN7

FN12

05

Addr7

City

State

ZIP

D

11​

0.151​

LN9

FN14

2​

Addr9

City

State

ZIP

D

15​

0.158​

LN12

FN19

1​

Addr12

City

State

ZIP

D

16​

0.158​

LN13

FN20

4​

Addr13

City

State

ZIP

D

17​

0.158​

LN14

FN21

4​

Addr14

City

State

ZIP

D

5​

0.119​

LN4

FN5

07

Addr4

City

State

ZIP

H

8​

0.13​

LN6

FN11

8​

Addr6

City

State

ZIP

H

10​

0.132​

LN8

FN13

08

Addr8

City

State

ZIP

H

6​

0.119​

LN4

FN6

03

Addr4

City

State

ZIP

O

6​

0.119​

LN4

FN7

3​

Addr4

City

State

ZIP

O

6​

0.119​

LN4

FN8

03

Addr4

City

State

ZIP

O

6​

0.119​

LN4

FN9

3​

Addr4

City

State

ZIP

O

13​

0.156​

LN11

FN16

01

Addr11

City

State

ZIP

P

14​

0.156​

LN11

FN17

2​

Addr11

City

State

ZIP

P

14​

0.156​

LN11

FN18

02

Addr11

City

State

ZIP

P

18​

0.162​

LN15

FN22

4​

Addr15

City

State

ZIP

P

19​

0.164​

LN16

FN23

A

Addr16

City

State

ZIP

B

 

[sunrise06]

This is close but if you look at line 4 you have Addr3 on the same line as Addr5. I need all names at the same Address on the same line. Thoughts?

Thanks.

 

[sandy666]

line the same but different ID
so ?

 

[sunrise06]

I am not understanding you response. Can you elaborate?

 

[sandy666]

there are groups, each group has own IDs

eg

ID.1

Distance.1

Last Name.1

First Name.1

Level.1

Mail Street Number1.1

Mail Street Number2.1

Mail City.1

Mail State.1

Mail Zip Code.1

Building.1

ID.2

Distance.2

Last Name.2

First Name.2

Level.2

Mail Street Number1.2

Mail Street Number2.2

Mail City.2

Mail State.2

Mail Zip Code.2

1​

0.072​

LN1

FN1

07

Addr1

City

State

ZIP

R

2​

0.111​

LN2

FN2

01

Addr2

City

State

ZIP

K

3​

0.111​

LN2

FN3

3​

Addr2

City

State

ZIP

4​

0.114​

LN3

FN4

05

Addr3

2Addr3

City

State

ZIP

D

7​

0.12​

LN5

FN10

2​

Addr5

2Addr7

City

State

ZIP

5​

0.119​

LN4

FN5

07

Addr4

City

State

ZIP

H

8​

0.13​

LN6

FN11

8​

Addr6

City

State

ZIP

6​

0.119​

LN4

FN6

03

Addr4

City

State

ZIP

O

6​

0.119​

LN4

FN7

3​

Addr4

City

State

ZIP

13​

0.156​

LN11

FN16

01

Addr11

City

State

ZIP

P

14​

0.156​

LN11

FN17

2​

Addr11

City

State

ZIP

19​

0.164​

LN16

FN23

A

Addr16

City

State

ZIP

B

and so on