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

  • Thread starter Thread starter William Martin
  • Start date Start date
W

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
 
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
Click to expand...
Store
1 1234567890
2 1234567891 1234567892 1234567893 1234567894
3 1234567895 1234567896 1234567897
4 1234567898 1234567899 1234567900
5 1234567901
Click to expand...

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.
 
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
Click to expand...

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
 
Hi William,

Am Fri, 13 Jun 2014 11:06:44 -0700 (PDT) schrieb William Martin:
Worked like a charm
Click to expand...

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


Regards
Claus B.
 
Back
Top