Unique #s in column, need multiple associated numbers in columns

[William Martin]

Hi guys,

I need some help with an excel problem. I have stores in column a, and phone numbers in column b. I need to make the store numbers unique in column a,which I can do. But I don't know how to go from the top table below to thebottom one. I can use access if need be, but I haven't been able to figureout how to do it their either.

Thanks for your help.

William

What I have:

Store Phone Number
1 1234567890
2 1234567891
2 1234567892
2 1234567893
2 1234567894
3 1234567895
3 1234567896
3 1234567897
4 1234567898
4 1234567899
4 1234567900
5 1234567901
5 1234567902

What I need:


Store
1 1234567890
2 1234567891 1234567892 1234567893 1234567894
3 1234567895 1234567896 1234567897
4 1234567898 1234567899 1234567900
5 1234567901

 

[Claus Busch]

Hi William,

Am Fri, 13 Jun 2014 09:53:16 -0700 (PDT) schrieb William Martin:

Store Phone Number
1 1234567890
2 1234567891
2 1234567892
2 1234567893
2 1234567894
3 1234567895
3 1234567896
3 1234567897
4 1234567898
4 1234567899
4 1234567900
5 1234567901
5 1234567902

Store
1 1234567890
2 1234567891 1234567892 1234567893 1234567894
3 1234567895 1234567896 1234567897
4 1234567898 1234567899 1234567900
5 1234567901

make your stores unique in Sheet2. Then in B2 of Sheet2:
=IFERROR(INDEX(Sheet1!$B:$B,SMALL(IF(Sheet1!$A$1:$A$100=$A2,ROW($1:$100)),COLUMN(A1))),"")
and enter the array formula with CTRL+Shift+Enter
and copy down as far as needed and to the right till the cells remain
blank.
If you have a lot of data the array formula will be slow. Then you have
do do it with a macro.


Regards
Claus B.

 

[William Martin]

Hi William,



Am Fri, 13 Jun 2014 09:53:16 -0700 (PDT) schrieb William Martin:






make your stores unique in Sheet2. Then in B2 of Sheet2:

=IFERROR(INDEX(Sheet1!$B:$B,SMALL(IF(Sheet1!$A$1:$A$100=$A2,ROW($1:$100)),COLUMN(A1))),"")

and enter the array formula with CTRL+Shift+Enter

and copy down as far as needed and to the right till the cells remain

blank.

If you have a lot of data the array formula will be slow. Then you have

do do it with a macro.





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional

Thank you for the help Claus, I do have quite a bit of data. Sheet1 has 24038 rows, and 6964 of them are unique, so there are 6965 rows in Sheet2. Would you mind helping me with the macro for it?

Thanks so much.

-William

 

[Claus Busch]

Hi William,

Am Fri, 13 Jun 2014 10:20:55 -0700 (PDT) schrieb William Martin:

I do have quite a bit of data. Sheet1 has 24038 rows, and 6964 of them are unique, so there are 6965 rows in Sheet2. Would you mind helping me with the macro for it?

have a look:
https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for "Sorting"
Rightclick and download the file because macros are disabled in OneDrive


Regards
Claus B.

 

[William Martin]

Hi William,



Am Fri, 13 Jun 2014 10:20:55 -0700 (PDT) schrieb William Martin:






have a look:

https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326

for "Sorting"

Rightclick and download the file because macros are disabled in OneDrive





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional

Worked like a charm. Thanks so much Claus!

 

[Claus Busch]

Hi William,

Am Fri, 13 Jun 2014 11:06:44 -0700 (PDT) schrieb William Martin:

Worked like a charm

I added another macro and uploaded the file again. Did you try macro
"Sorting2"? That macro should be faster.


Regards
Claus B.