find multiple data cells for one data source

I  need a formula that will help me organize country codes.  In Column A, I 
have 3 digit codes that have a country code, in column B, associated with it. 
 The 3 digit codes appear multiple times in the spreadsheet.  I need a way to 
show the 3 digit code and any and all of the country codes that are 
associated with it on one row.

Example of my raw data below:
ABF	CN
ABF	MX
ABF	US
ACC	CN
ACC	IE
ACC	MX
ACC	TW
ACC	US

Desired Output:
Col A      Col B  Col C   Col D  Col E  Col F
ABF        CN       MX     US
ACC        CN      IE        MX    TW     US


Satisfactory output:
ABF        CN, MX, US
ACC        CN, IE, MX, TW, US


0
Utf
2/18/2010 4:34:01 AM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
825 Views

Similar Articles

[PageSpeed] 14

With data in Sheet1 ColA/ColB

With ABF in Sheet2 cell A1 ; try the below formula in Sheet2 cell B1 and 
copy across as required..

Please note that this is an array formula. You create array formulas in the 
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to 
enter the formula. If successful in 'Formula Bar' you can notice the curly 
braces at both ends like "{=<formula>}"

=IF(COUNTIF(Sheet1!$A$1:$A$1000,$A1)<COLUMN(A1),"",
INDEX(Sheet1!$B1:$B1000,SMALL(IF(Sheet1!$A$1:$A$1000=$A1,
ROW($A$1:$A$1000)),COLUMN(A1))))

-- 
Jacob


"brantty" wrote:

> I  need a formula that will help me organize country codes.  In Column A, I 
> have 3 digit codes that have a country code, in column B, associated with it. 
>  The 3 digit codes appear multiple times in the spreadsheet.  I need a way to 
> show the 3 digit code and any and all of the country codes that are 
> associated with it on one row.
> 
> Example of my raw data below:
> ABF	CN
> ABF	MX
> ABF	US
> ACC	CN
> ACC	IE
> ACC	MX
> ACC	TW
> ACC	US
> 
> Desired Output:
> Col A      Col B  Col C   Col D  Col E  Col F
> ABF        CN       MX     US
> ACC        CN      IE        MX    TW     US
> 
> 
> Satisfactory output:
> ABF        CN, MX, US
> ACC        CN, IE, MX, TW, US
> 
> 
0
Utf
2/18/2010 4:52:01 AM
Jacob,

Im new to array formulas.  I copied the formula, and it finds the first 
code, but that is all.  When I copied it across to columns, c,d,e, etc,  I 
just get a #NUM!.  Can you dumb this down for me?

Ty

"Jacob Skaria" wrote:

> With data in Sheet1 ColA/ColB
> 
> With ABF in Sheet2 cell A1 ; try the below formula in Sheet2 cell B1 and 
> copy across as required..
> 
> Please note that this is an array formula. You create array formulas in the 
> same way that you create other formulas, except you press CTRL+SHIFT+ENTER to 
> enter the formula. If successful in 'Formula Bar' you can notice the curly 
> braces at both ends like "{=<formula>}"
> 
> =IF(COUNTIF(Sheet1!$A$1:$A$1000,$A1)<COLUMN(A1),"",
> INDEX(Sheet1!$B1:$B1000,SMALL(IF(Sheet1!$A$1:$A$1000=$A1,
> ROW($A$1:$A$1000)),COLUMN(A1))))
> 
> -- 
> Jacob
> 
> 
> "brantty" wrote:
> 
> > I  need a formula that will help me organize country codes.  In Column A, I 
> > have 3 digit codes that have a country code, in column B, associated with it. 
> >  The 3 digit codes appear multiple times in the spreadsheet.  I need a way to 
> > show the 3 digit code and any and all of the country codes that are 
> > associated with it on one row.
> > 
> > Example of my raw data below:
> > ABF	CN
> > ABF	MX
> > ABF	US
> > ACC	CN
> > ACC	IE
> > ACC	MX
> > ACC	TW
> > ACC	US
> > 
> > Desired Output:
> > Col A      Col B  Col C   Col D  Col E  Col F
> > ABF        CN       MX     US
> > ACC        CN      IE        MX    TW     US
> > 
> > 
> > Satisfactory output:
> > ABF        CN, MX, US
> > ACC        CN, IE, MX, TW, US
> > 
> > 
0
Utf
2/18/2010 5:34:01 AM
Just try again. Do you see the curly braces around the formula. Press 
CTRL+SHIFT+ENTER instead of just ENTER . 

-- 
Jacob


"brantty" wrote:

> Jacob,
> 
> Im new to array formulas.  I copied the formula, and it finds the first 
> code, but that is all.  When I copied it across to columns, c,d,e, etc,  I 
> just get a #NUM!.  Can you dumb this down for me?
> 
> Ty
> 
> "Jacob Skaria" wrote:
> 
> > With data in Sheet1 ColA/ColB
> > 
> > With ABF in Sheet2 cell A1 ; try the below formula in Sheet2 cell B1 and 
> > copy across as required..
> > 
> > Please note that this is an array formula. You create array formulas in the 
> > same way that you create other formulas, except you press CTRL+SHIFT+ENTER to 
> > enter the formula. If successful in 'Formula Bar' you can notice the curly 
> > braces at both ends like "{=<formula>}"
> > 
> > =IF(COUNTIF(Sheet1!$A$1:$A$1000,$A1)<COLUMN(A1),"",
> > INDEX(Sheet1!$B1:$B1000,SMALL(IF(Sheet1!$A$1:$A$1000=$A1,
> > ROW($A$1:$A$1000)),COLUMN(A1))))
> > 
> > -- 
> > Jacob
> > 
> > 
> > "brantty" wrote:
> > 
> > > I  need a formula that will help me organize country codes.  In Column A, I 
> > > have 3 digit codes that have a country code, in column B, associated with it. 
> > >  The 3 digit codes appear multiple times in the spreadsheet.  I need a way to 
> > > show the 3 digit code and any and all of the country codes that are 
> > > associated with it on one row.
> > > 
> > > Example of my raw data below:
> > > ABF	CN
> > > ABF	MX
> > > ABF	US
> > > ACC	CN
> > > ACC	IE
> > > ACC	MX
> > > ACC	TW
> > > ACC	US
> > > 
> > > Desired Output:
> > > Col A      Col B  Col C   Col D  Col E  Col F
> > > ABF        CN       MX     US
> > > ACC        CN      IE        MX    TW     US
> > > 
> > > 
> > > Satisfactory output:
> > > ABF        CN, MX, US
> > > ACC        CN, IE, MX, TW, US
> > > 
> > > 
0
Utf
2/18/2010 5:56:04 AM
Jacob,

I see the curly braces now.  But the formula doesnt seem to find all the 
data on the second item.  In the example below.  my results show:

ABF	CN	MX	US		
ACC	MX	MX	TW	US	0

Its missing the first two codes.  But could it have something to do with the 
fact that my Sheet 2, col A, has the 3 digit code showing once,(no 
duplicates) and the Sheet 1 has many duplicates.  So, Sheet 1 Column A will 
not match Sheet 2 column A.

Ty

"Jacob Skaria" wrote:

> Just try again. Do you see the curly braces around the formula. Press 
> CTRL+SHIFT+ENTER instead of just ENTER . 
> 
> -- 
> Jacob
> 
> 
> "brantty" wrote:
> 
> > Jacob,
> > 
> > Im new to array formulas.  I copied the formula, and it finds the first 
> > code, but that is all.  When I copied it across to columns, c,d,e, etc,  I 
> > just get a #NUM!.  Can you dumb this down for me?
> > 
> > Ty
> > 
> > "Jacob Skaria" wrote:
> > 
> > > With data in Sheet1 ColA/ColB
> > > 
> > > With ABF in Sheet2 cell A1 ; try the below formula in Sheet2 cell B1 and 
> > > copy across as required..
> > > 
> > > Please note that this is an array formula. You create array formulas in the 
> > > same way that you create other formulas, except you press CTRL+SHIFT+ENTER to 
> > > enter the formula. If successful in 'Formula Bar' you can notice the curly 
> > > braces at both ends like "{=<formula>}"
> > > 
> > > =IF(COUNTIF(Sheet1!$A$1:$A$1000,$A1)<COLUMN(A1),"",
> > > INDEX(Sheet1!$B1:$B1000,SMALL(IF(Sheet1!$A$1:$A$1000=$A1,
> > > ROW($A$1:$A$1000)),COLUMN(A1))))
> > > 
> > > -- 
> > > Jacob
> > > 
> > > 
> > > "brantty" wrote:
> > > 
> > > > I  need a formula that will help me organize country codes.  In Column A, I 
> > > > have 3 digit codes that have a country code, in column B, associated with it. 
> > > >  The 3 digit codes appear multiple times in the spreadsheet.  I need a way to 
> > > > show the 3 digit code and any and all of the country codes that are 
> > > > associated with it on one row.
> > > > 
> > > > Example of my raw data below:
> > > > ABF	CN
> > > > ABF	MX
> > > > ABF	US
> > > > ACC	CN
> > > > ACC	IE
> > > > ACC	MX
> > > > ACC	TW
> > > > ACC	US
> > > > 
> > > > Desired Output:
> > > > Col A      Col B  Col C   Col D  Col E  Col F
> > > > ABF        CN       MX     US
> > > > ACC        CN      IE        MX    TW     US
> > > > 
> > > > 
> > > > Satisfactory output:
> > > > ABF        CN, MX, US
> > > > ACC        CN, IE, MX, TW, US
> > > > 
> > > > 
0
Utf
2/18/2010 6:28:01 AM
I missed a $ (absolute reference)..Try with the below in cell B1 and copy 
down/across as required

=IF(COUNTIF(Sheet1!$A$1:$A$1000,$A1)<COLUMN(A1),"",
INDEX(Sheet1!$B$1:$B$1000,SMALL(IF(Sheet1!$A$1:$A$1000=$A1,
ROW($A$1:$A$1000)),COLUMN(A1))))

-- 
Jacob


"brantty" wrote:

> Jacob,
> 
> I see the curly braces now.  But the formula doesnt seem to find all the 
> data on the second item.  In the example below.  my results show:
> 
> ABF	CN	MX	US		
> ACC	MX	MX	TW	US	0
> 
> Its missing the first two codes.  But could it have something to do with the 
> fact that my Sheet 2, col A, has the 3 digit code showing once,(no 
> duplicates) and the Sheet 1 has many duplicates.  So, Sheet 1 Column A will 
> not match Sheet 2 column A.
> 
> Ty
> 
> "Jacob Skaria" wrote:
> 
> > Just try again. Do you see the curly braces around the formula. Press 
> > CTRL+SHIFT+ENTER instead of just ENTER . 
> > 
> > -- 
> > Jacob
> > 
> > 
> > "brantty" wrote:
> > 
> > > Jacob,
> > > 
> > > Im new to array formulas.  I copied the formula, and it finds the first 
> > > code, but that is all.  When I copied it across to columns, c,d,e, etc,  I 
> > > just get a #NUM!.  Can you dumb this down for me?
> > > 
> > > Ty
> > > 
> > > "Jacob Skaria" wrote:
> > > 
> > > > With data in Sheet1 ColA/ColB
> > > > 
> > > > With ABF in Sheet2 cell A1 ; try the below formula in Sheet2 cell B1 and 
> > > > copy across as required..
> > > > 
> > > > Please note that this is an array formula. You create array formulas in the 
> > > > same way that you create other formulas, except you press CTRL+SHIFT+ENTER to 
> > > > enter the formula. If successful in 'Formula Bar' you can notice the curly 
> > > > braces at both ends like "{=<formula>}"
> > > > 
> > > > =IF(COUNTIF(Sheet1!$A$1:$A$1000,$A1)<COLUMN(A1),"",
> > > > INDEX(Sheet1!$B1:$B1000,SMALL(IF(Sheet1!$A$1:$A$1000=$A1,
> > > > ROW($A$1:$A$1000)),COLUMN(A1))))
> > > > 
> > > > -- 
> > > > Jacob
> > > > 
> > > > 
> > > > "brantty" wrote:
> > > > 
> > > > > I  need a formula that will help me organize country codes.  In Column A, I 
> > > > > have 3 digit codes that have a country code, in column B, associated with it. 
> > > > >  The 3 digit codes appear multiple times in the spreadsheet.  I need a way to 
> > > > > show the 3 digit code and any and all of the country codes that are 
> > > > > associated with it on one row.
> > > > > 
> > > > > Example of my raw data below:
> > > > > ABF	CN
> > > > > ABF	MX
> > > > > ABF	US
> > > > > ACC	CN
> > > > > ACC	IE
> > > > > ACC	MX
> > > > > ACC	TW
> > > > > ACC	US
> > > > > 
> > > > > Desired Output:
> > > > > Col A      Col B  Col C   Col D  Col E  Col F
> > > > > ABF        CN       MX     US
> > > > > ACC        CN      IE        MX    TW     US
> > > > > 
> > > > > 
> > > > > Satisfactory output:
> > > > > ABF        CN, MX, US
> > > > > ACC        CN, IE, MX, TW, US
> > > > > 
> > > > > 
0
Utf
2/18/2010 6:40:01 AM
Jacob,  Its close, man.  The first item (ABF) comes out clean, but when it 
looks for the second item (ACC), it doesnt find the first code (CN) and 
duplicates the find of MX.  Got any other ideas....?

Ty

"Jacob Skaria" wrote:

> I missed a $ (absolute reference)..Try with the below in cell B1 and copy 
> down/across as required
> 
> =IF(COUNTIF(Sheet1!$A$1:$A$1000,$A1)<COLUMN(A1),"",
> INDEX(Sheet1!$B$1:$B$1000,SMALL(IF(Sheet1!$A$1:$A$1000=$A1,
> ROW($A$1:$A$1000)),COLUMN(A1))))
> 
> -- 
> Jacob
> 
> 
> "brantty" wrote:
> 
> > Jacob,
> > 
> > I see the curly braces now.  But the formula doesnt seem to find all the 
> > data on the second item.  In the example below.  my results show:
> > 
> > ABF	CN	MX	US		
> > ACC	MX	MX	TW	US	0
> > 
> > Its missing the first two codes.  But could it have something to do with the 
> > fact that my Sheet 2, col A, has the 3 digit code showing once,(no 
> > duplicates) and the Sheet 1 has many duplicates.  So, Sheet 1 Column A will 
> > not match Sheet 2 column A.
> > 
> > Ty
> > 
> > "Jacob Skaria" wrote:
> > 
> > > Just try again. Do you see the curly braces around the formula. Press 
> > > CTRL+SHIFT+ENTER instead of just ENTER . 
> > > 
> > > -- 
> > > Jacob
> > > 
> > > 
> > > "brantty" wrote:
> > > 
> > > > Jacob,
> > > > 
> > > > Im new to array formulas.  I copied the formula, and it finds the first 
> > > > code, but that is all.  When I copied it across to columns, c,d,e, etc,  I 
> > > > just get a #NUM!.  Can you dumb this down for me?
> > > > 
> > > > Ty
> > > > 
> > > > "Jacob Skaria" wrote:
> > > > 
> > > > > With data in Sheet1 ColA/ColB
> > > > > 
> > > > > With ABF in Sheet2 cell A1 ; try the below formula in Sheet2 cell B1 and 
> > > > > copy across as required..
> > > > > 
> > > > > Please note that this is an array formula. You create array formulas in the 
> > > > > same way that you create other formulas, except you press CTRL+SHIFT+ENTER to 
> > > > > enter the formula. If successful in 'Formula Bar' you can notice the curly 
> > > > > braces at both ends like "{=<formula>}"
> > > > > 
> > > > > =IF(COUNTIF(Sheet1!$A$1:$A$1000,$A1)<COLUMN(A1),"",
> > > > > INDEX(Sheet1!$B1:$B1000,SMALL(IF(Sheet1!$A$1:$A$1000=$A1,
> > > > > ROW($A$1:$A$1000)),COLUMN(A1))))
> > > > > 
> > > > > -- 
> > > > > Jacob
> > > > > 
> > > > > 
> > > > > "brantty" wrote:
> > > > > 
> > > > > > I  need a formula that will help me organize country codes.  In Column A, I 
> > > > > > have 3 digit codes that have a country code, in column B, associated with it. 
> > > > > >  The 3 digit codes appear multiple times in the spreadsheet.  I need a way to 
> > > > > > show the 3 digit code and any and all of the country codes that are 
> > > > > > associated with it on one row.
> > > > > > 
> > > > > > Example of my raw data below:
> > > > > > ABF	CN
> > > > > > ABF	MX
> > > > > > ABF	US
> > > > > > ACC	CN
> > > > > > ACC	IE
> > > > > > ACC	MX
> > > > > > ACC	TW
> > > > > > ACC	US
> > > > > > 
> > > > > > Desired Output:
> > > > > > Col A      Col B  Col C   Col D  Col E  Col F
> > > > > > ABF        CN       MX     US
> > > > > > ACC        CN      IE        MX    TW     US
> > > > > > 
> > > > > > 
> > > > > > Satisfactory output:
> > > > > > ABF        CN, MX, US
> > > > > > ACC        CN, IE, MX, TW, US
> > > > > > 
> > > > > > 
0
Utf
2/18/2010 6:49:01 AM
I just tried with your sample data and is working fine. Re-try with the last 
formula (array formula in cell B1) and copy down/across...

-- 
Jacob


"brantty" wrote:

> Jacob,  Its close, man.  The first item (ABF) comes out clean, but when it 
> looks for the second item (ACC), it doesnt find the first code (CN) and 
> duplicates the find of MX.  Got any other ideas....?
> 
> Ty
> 
> "Jacob Skaria" wrote:
> 
> > I missed a $ (absolute reference)..Try with the below in cell B1 and copy 
> > down/across as required
> > 
> > =IF(COUNTIF(Sheet1!$A$1:$A$1000,$A1)<COLUMN(A1),"",
> > INDEX(Sheet1!$B$1:$B$1000,SMALL(IF(Sheet1!$A$1:$A$1000=$A1,
> > ROW($A$1:$A$1000)),COLUMN(A1))))
> > 
> > -- 
> > Jacob
> > 
> > 
> > "brantty" wrote:
> > 
> > > Jacob,
> > > 
> > > I see the curly braces now.  But the formula doesnt seem to find all the 
> > > data on the second item.  In the example below.  my results show:
> > > 
> > > ABF	CN	MX	US		
> > > ACC	MX	MX	TW	US	0
> > > 
> > > Its missing the first two codes.  But could it have something to do with the 
> > > fact that my Sheet 2, col A, has the 3 digit code showing once,(no 
> > > duplicates) and the Sheet 1 has many duplicates.  So, Sheet 1 Column A will 
> > > not match Sheet 2 column A.
> > > 
> > > Ty
> > > 
> > > "Jacob Skaria" wrote:
> > > 
> > > > Just try again. Do you see the curly braces around the formula. Press 
> > > > CTRL+SHIFT+ENTER instead of just ENTER . 
> > > > 
> > > > -- 
> > > > Jacob
> > > > 
> > > > 
> > > > "brantty" wrote:
> > > > 
> > > > > Jacob,
> > > > > 
> > > > > Im new to array formulas.  I copied the formula, and it finds the first 
> > > > > code, but that is all.  When I copied it across to columns, c,d,e, etc,  I 
> > > > > just get a #NUM!.  Can you dumb this down for me?
> > > > > 
> > > > > Ty
> > > > > 
> > > > > "Jacob Skaria" wrote:
> > > > > 
> > > > > > With data in Sheet1 ColA/ColB
> > > > > > 
> > > > > > With ABF in Sheet2 cell A1 ; try the below formula in Sheet2 cell B1 and 
> > > > > > copy across as required..
> > > > > > 
> > > > > > Please note that this is an array formula. You create array formulas in the 
> > > > > > same way that you create other formulas, except you press CTRL+SHIFT+ENTER to 
> > > > > > enter the formula. If successful in 'Formula Bar' you can notice the curly 
> > > > > > braces at both ends like "{=<formula>}"
> > > > > > 
> > > > > > =IF(COUNTIF(Sheet1!$A$1:$A$1000,$A1)<COLUMN(A1),"",
> > > > > > INDEX(Sheet1!$B1:$B1000,SMALL(IF(Sheet1!$A$1:$A$1000=$A1,
> > > > > > ROW($A$1:$A$1000)),COLUMN(A1))))
> > > > > > 
> > > > > > -- 
> > > > > > Jacob
> > > > > > 
> > > > > > 
> > > > > > "brantty" wrote:
> > > > > > 
> > > > > > > I  need a formula that will help me organize country codes.  In Column A, I 
> > > > > > > have 3 digit codes that have a country code, in column B, associated with it. 
> > > > > > >  The 3 digit codes appear multiple times in the spreadsheet.  I need a way to 
> > > > > > > show the 3 digit code and any and all of the country codes that are 
> > > > > > > associated with it on one row.
> > > > > > > 
> > > > > > > Example of my raw data below:
> > > > > > > ABF	CN
> > > > > > > ABF	MX
> > > > > > > ABF	US
> > > > > > > ACC	CN
> > > > > > > ACC	IE
> > > > > > > ACC	MX
> > > > > > > ACC	TW
> > > > > > > ACC	US
> > > > > > > 
> > > > > > > Desired Output:
> > > > > > > Col A      Col B  Col C   Col D  Col E  Col F
> > > > > > > ABF        CN       MX     US
> > > > > > > ACC        CN      IE        MX    TW     US
> > > > > > > 
> > > > > > > 
> > > > > > > Satisfactory output:
> > > > > > > ABF        CN, MX, US
> > > > > > > ACC        CN, IE, MX, TW, US
> > > > > > > 
> > > > > > > 
0
Utf
2/18/2010 6:53:01 AM
Jacob,  It worked.  I had to play with it a little more.  Thanks

"Jacob Skaria" wrote:

> I missed a $ (absolute reference)..Try with the below in cell B1 and copy 
> down/across as required
> 
> =IF(COUNTIF(Sheet1!$A$1:$A$1000,$A1)<COLUMN(A1),"",
> INDEX(Sheet1!$B$1:$B$1000,SMALL(IF(Sheet1!$A$1:$A$1000=$A1,
> ROW($A$1:$A$1000)),COLUMN(A1))))
> 
> -- 
> Jacob
> 
> 
> "brantty" wrote:
> 
> > Jacob,
> > 
> > I see the curly braces now.  But the formula doesnt seem to find all the 
> > data on the second item.  In the example below.  my results show:
> > 
> > ABF	CN	MX	US		
> > ACC	MX	MX	TW	US	0
> > 
> > Its missing the first two codes.  But could it have something to do with the 
> > fact that my Sheet 2, col A, has the 3 digit code showing once,(no 
> > duplicates) and the Sheet 1 has many duplicates.  So, Sheet 1 Column A will 
> > not match Sheet 2 column A.
> > 
> > Ty
> > 
> > "Jacob Skaria" wrote:
> > 
> > > Just try again. Do you see the curly braces around the formula. Press 
> > > CTRL+SHIFT+ENTER instead of just ENTER . 
> > > 
> > > -- 
> > > Jacob
> > > 
> > > 
> > > "brantty" wrote:
> > > 
> > > > Jacob,
> > > > 
> > > > Im new to array formulas.  I copied the formula, and it finds the first 
> > > > code, but that is all.  When I copied it across to columns, c,d,e, etc,  I 
> > > > just get a #NUM!.  Can you dumb this down for me?
> > > > 
> > > > Ty
> > > > 
> > > > "Jacob Skaria" wrote:
> > > > 
> > > > > With data in Sheet1 ColA/ColB
> > > > > 
> > > > > With ABF in Sheet2 cell A1 ; try the below formula in Sheet2 cell B1 and 
> > > > > copy across as required..
> > > > > 
> > > > > Please note that this is an array formula. You create array formulas in the 
> > > > > same way that you create other formulas, except you press CTRL+SHIFT+ENTER to 
> > > > > enter the formula. If successful in 'Formula Bar' you can notice the curly 
> > > > > braces at both ends like "{=<formula>}"
> > > > > 
> > > > > =IF(COUNTIF(Sheet1!$A$1:$A$1000,$A1)<COLUMN(A1),"",
> > > > > INDEX(Sheet1!$B1:$B1000,SMALL(IF(Sheet1!$A$1:$A$1000=$A1,
> > > > > ROW($A$1:$A$1000)),COLUMN(A1))))
> > > > > 
> > > > > -- 
> > > > > Jacob
> > > > > 
> > > > > 
> > > > > "brantty" wrote:
> > > > > 
> > > > > > I  need a formula that will help me organize country codes.  In Column A, I 
> > > > > > have 3 digit codes that have a country code, in column B, associated with it. 
> > > > > >  The 3 digit codes appear multiple times in the spreadsheet.  I need a way to 
> > > > > > show the 3 digit code and any and all of the country codes that are 
> > > > > > associated with it on one row.
> > > > > > 
> > > > > > Example of my raw data below:
> > > > > > ABF	CN
> > > > > > ABF	MX
> > > > > > ABF	US
> > > > > > ACC	CN
> > > > > > ACC	IE
> > > > > > ACC	MX
> > > > > > ACC	TW
> > > > > > ACC	US
> > > > > > 
> > > > > > Desired Output:
> > > > > > Col A      Col B  Col C   Col D  Col E  Col F
> > > > > > ABF        CN       MX     US
> > > > > > ACC        CN      IE        MX    TW     US
> > > > > > 
> > > > > > 
> > > > > > Satisfactory output:
> > > > > > ABF        CN, MX, US
> > > > > > ACC        CN, IE, MX, TW, US
> > > > > > 
> > > > > > 
0
Utf
2/18/2010 7:01:01 AM
Reply:

Similar Artilces:

Excel VB-Copy formula down until adjacent cell (left) is blank?
Here is exactly what I am trying to do through VB in Excel: Weekly data pull fills colums A:G. Row count is always different. I am modifying the data pull through VB, and I have a VLOOKUP formula in cell H2. What I want VB to do is copy that formula down column H to the last row (with data) each week. I guess I want it to be dynamic so that as rows decrease/increase the formula is only copied down to the final row/record. I know someone out of this smart group will know how to do this! Thanks in advance! Tony (pseudo code) in a macro .... dim lngLastrow as long dim rngTarget...

Off by one transaction
When we are posting to the General Legder, the control # of transactions are off exactly one transaction to the actual # of transactions. We don't understand why this is so because we physically counted the transactions and the actual # of transactions are correct but the control # transactions are off by one. We posted anyway because we physically counted them, but for future use we would like to know why the # of transactions are off by one. If you have an answer to our dilemma please respond back. Much Appreciated, Matt Barran Matt: The Control Total is a number you enter,...

Is there a quick way of assigning multiple series to a bubble char
Each series requires a three-column block of cells, one each for X, Y, and bubble size. Select the block for the first series and create the chart. Select the block for the second series, copy it, select the chart, use Edit menu - Paste Special to add the data as a New Series, with categories in the first column. Repeat as needed. If the series share the X values, you can use this sequence of columns: X Y1 B1 Y2 B2 Y3 B3 where X is the column of shared X values, Yi is the column of Y values for the ith series, and Bi is the column of bubble sizes for the ith series. - Jon -----...

Sorting numbers with multiple decimal points?
Hi - I'm sure this is covered in some docs somewhere, but it's hard to know what to search for. I have a spreadsheet (using Excel 2003 SP1) in which I have a number of rows that have an "ID" field taking the format "X.X.X.X" where X is a number from 0 to 100. Like this: 1.1.0.0 blah blah rest of row 1.1.2.4 blah blah rest of row 1.1.3.0 blah blah rest of row 1.0.0.0 blah blah rest of row 1.1.3.2 blah blah rest of row 1.1.3.1 blah blah rest of row 1.1.1.1 blah blah rest of row 1.1.3.0 blah blah rest of row 1.1.1.3 blah blah rest of row 1.1.2.5 blah blah rest of ...

One column with different widths
I want to make Column A with rows 1-29 one width and rows 30 and above another width. -- Calpitor Excel does not allow varying widths of cells within a column (or heights within rows). You could used 'Merged' cells to accomodate the wider column needs. HTH -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15101 View this thread: http://www.excelforum.com/showthread.php?threadid=476150 ...

Q: Referencing named cells in external worksheet ?
Using Excel 2002. I have a workbook with 12 worksheets (one for every month of the year), wherein a lot of the information is looked-up (using VLOOKUP) in simples arrays. I saw no point in implementing the arrays as a 13th worksheet, because I will have a yearly version of my monthly worksheets in one workbook (so one for 2003, 2004, etc). If I change the array(s), I want them to be reflected in all referencing cells. Problem: If my workbook containing my arrays (called "Global") is loaded, I have no problem and the references to it read as: (blabla) 'Global.xls'!Roster ...

Cutting and Pasteing from One spreadsheet to another.
Im trying to make up several templates that contain simliar data references. I want to cut from one template to another a number of cells. eg I have the following in one spreadsheet. =Info!G5 =Info!G6 =Info!G7 =Info!G8 =Info!G9 =Info!G10 =Info!G11 etc I simply want to cut this text exactly as it is and paste it into another spread sheet. However when I do I get the following ='C:\Documents and Settings\All Users\Templates\_Test_Excel_Access_Merge\[S8-R200a.xls]Info'!G5 ='C:\Documents and Settings\All Users\Templates\_Test_Excel_Access_Merge\[S8-R200a.xls]Info'!G6 etc. Re...

Visible cell characters
Can I increase the # of characters that are visible in a cell? 67 Excel Help on "limits" or "specifications" reveals that Excel will allow 32,767 characters to be entered in a cell. However, it goes on to state that "only 1024 characters will be visible or can be printed" To work around this limitation, stick a few ALT + ENTERs in at appropriate spots. The ALT + ENTER forces a line-feed and expands the 1024 limit. How far is not really known. Just experiment. .........From Dave Peterson.......... I put this formula in A1: ="xxx"& REPT(REPT(&...

In excel when inputting dates 10/4/04 (10/april/04) data converts.
In excel when inputting dates 10/4/04 (10/April/2004) excel converts this to 4/10/04 (4/October/2004) Where can I change the input characteristics. I have changed the date format to English (UK) and this works when I input the date in the American format mm/dd/yy but I want to input in the dd/mm/yy format I think I'd try chaning my windows regional settings. In win98, I'd get to it via: Windows start button|Settings|control Panel|regional settings applet Date Tab Change the short date format to dd/mm/yyyy (I like 4 digit dates--you don't have to use that part--but put it in d...

Cell Reference Problem with Network
Let's say I have a spreadsheet with two worksheets = SheetA and SheetB. SheetA might reference a cell in SheetB with a formula like =SheetB!A1 But when I move this to the network the reference changes to include the network drive and file name like: ='Z:\FOLDER\[FILE]SheetB!A1 the file may move from my laptop to the network several times and this becomes completely confusion as the reference looks, not within the same spreadsheet which is what I want it to to, but for another file out on the network. How do I explicitly reference a cell within a difference worksheet but alwa...

CRM & Data Matching
Hi, We are going to populate our MSCRM with various data sets. Because of this there will be duplication of customer data. Is there any mechanism within Microsoft CRM (or easily available elsewhere) to match the data from the diverse sources against each other and to arrive at a single instance of the customer. Thanks, D. Microsoft CRM V3 only offers merge capabilities which means you have to locate the duplicates - crm can then merge the records two at a time. ISV's have produced other solutions ======================= John O'Donnell Microsoft CRM MVP http://codegallery.got...

How to generate a truly empty cell
"" generates a zero-length string, not a truly empty cell. This is causing problems elsewhere. I'd like to find an output for an IF statement that will give me a truly empty cell. The current formula is: =IF(COUNT(C24:C29)>0,SUM(C24:C29),"") Any ideas? If it involves a macro (as I think it might, having read other posts), please explain how to implement it. Thanks! <This is causing problems elsewhere> It shouldn't. Don't use ISBLANK(A1), use A1="" -- Kind regards, Niek Otten Microsoft MVP - Excel "paulkaye" <paulmjkaye@gm...

Splitting cells
in cell A1 i have the numbers 123456789. i want cell B1 to have numbers 1234567 and cell C1 to have just 89. what is the formula? i have tried text to columns If this is for extracting the first 7 characters use LEFT() =LEFT(A1,7) =RIGHT(A1,2) -- Jacob (MVP - Excel) "fazz" wrote: > in cell A1 i have the numbers 123456789. i want cell B1 to have numbers > 1234567 and cell C1 to have just 89. what is the formula? i have tried text > to columns Hello Jacob - i did not explain this very well.The digits in cell A1 is variable length. In cell C1 i n...

Calcs & Data Validation
Looking for an easy way to complet the following. I imagine their must be a quick & easy method. Cell A5 has a drop down list which corresponds to a range on another worksheet. It has 300+ entries. In cell B5 i will enter a number/qty. What i would like is that as each of the items in the list has a corresponding cost (on other worksheet), i would like to know an easy way to calculate the cost (in C5) based upon the qty entered (B5) & the cost of the item selected. I have done it before with 'IF' function for a small selection, but figure there must be an easier...

Non-VBA formula to find 2nd Sunday of a given month
Can anyone help me write a formula to find the date of the second sunda in a given month? Thanks in Advance, Dav ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com David, Assuming you have a date in A1, this gives the 2nd Sunday of that date =DATE(YEAR(A1),MONTH(A1),1)+MOD(8-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),7)+7 -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "DavidObeid&q...

Setting multiple meeting times for different attendees on the same day
I am a recruiter and use Outlook to schedule interviews with out of town candidates. It's critical that all my interviewers be available on the same date but at different times during the day. I would like to be able to pull up all of my interviewers' schedules at the same time and schedule them in different time slots but only having to send one meeting request. Example: I have 5 managers that will be interviewing the same person on the same day at different times. I have to list them all as "required attendees", check their schedules, then back out and send o...

How to print a multiple page two sided small booklet, 4.5x5.5"
Publisher 03 has a folded card that uses standard 8.5x11" paper and after printing folds to a card size 4.5 x 5.5" What I need is to create a non folded "booklet" version which can have multiple pages (more than 8), printed on front and back. Essentially the same as the 8.5x5" program booklets (found in File/Page Setup/publication type:booklet) which print multiple pages now, but smaller and capable of printing front to back pages to either be folded into a booklet (one fold on center pages) or cut and bound into a booklet. I'm trying to build a small boo...

address book not finding contact when putting in letters
I guess I have general questions about the find function on the top of the toolbar, I have a contact in the company field called netflix, when I type net in the find box, it does not show up, but if I type netflix, it does show up??? why is that , I have noticed this on several contacts Same thing with me I have a name and email address in the Outlook Contacts When sending an email, sometimes as I type the name the email appears, sometimes it does not "bob" <bob@donotspam> wrote in message news:epF4Pp1rEHA.4004@TK2MSFTNGP10.phx.gbl... >I guess I have general questions...

Pivotchart Multiple series question
Is it possible to have 2 separate clustered series in 1 pivotchart table and if so how. Regards Jason ...

making a sum outcome negative based on adjacent cell value
I have 3 columns of single figures. At present i'm using the sumproduc fuction to multiply and total the figures in column A that fall betwee 4 and 9 with the adjacent figure in column B... =SUMPRODUCT(--($A1:$A600>=4),--($A1:$A600<=9),($B1:$B600),($A1:$A600) I'd like to add column C to the formula, so that if it contained value of -1, 1 or 2, the sum of the adjacent figures in columns A and appears as a negative number. For example A3= 7, B3= 2, C3= 1 Outcome= -14 A4= 9, B4= 1, C4= 5 Outcome= 9 A5= 3, B5= 2, C5= 2 No sum because figure in column A ...

Formatting doesn't follow data sort
I was just recently updated to 2003 MS Office Pro. I can't remember what I use to have, but I used to be able to Data Sort info and the borders around cells would follow the appropriate text. It doesn't do that anymore, and I can't find out how to fix it in the online help, and my IT guy doesn't know off the top of his head. Can anyone help or have I wasted a full day and a half putting borders around information for no reason? -- kanimalhouse ------------------------------------------------------------------------ kanimalhouse's Profile: http://www.excelforum.com/me...

Pivot tables, linking to a named range as a source to a pivot table
I have created a main worksheet within the same workbook of many pivot tables, and I want to use this same worksheet as the source of information to these different pivot table sheets off of which I create charts. I want to use a named range because there are over 4,000 rows in this main worksheet. I am not sure if I need a "=" to start the reference or what to do. I thought I should just use nameofworksheet!database if I name the range "database", but when I point and click to the sheet, it is just inserting the name "database" without the name of the...

Merged Cells
I have imported data into Excel. The left-hand column has merged cells containing a reference number. The remaining columns contain varying records associated with the reference number, a one to many ratio. I need to display the worksheet so that the appropriate reference number is displayed in the left-hand column for each of the records in the worksheet. There are hundreds of reference numbers. Is there an automated way to do this besides unmerging each section and copying the reference number into the now unmerged cells? Thank you. ...

HOW to COUNT THE FREQUENCY of specific CHARACTER WITHIN a CELL?
How can a frequency of a specific character be counted with in a cell. Ex -" #4 Bluebirds, #6 Aquatic" is in cell B2 - how do I count the number of "#" that appear in cell B2? =LEN(B2)-LEN(SUBSTITUTE(B2,"#","")) HTH Jason Atlanta, GA >-----Original Message----- >How can a frequency of a specific character be counted with in a cell. Ex -" >#4 Bluebirds, #6 Aquatic" is in cell B2 - how do I count the number of "#" >that appear in cell B2? >. > ...

Make Smartlist Payables trx find unposted batches for reconcile
Smartlist currently does not view unposted payables batches. Many people want to use this object to review unposted batches and to assist in reconciling AP to the G/L or to dump AP transaction history for auditors. he Payables Transaction Smartlist object pulls the distribution accounts from the Vendor master and not the actual posted distribution. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, f...