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

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$10 00=$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

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$10 00=$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

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$10 00=$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

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$10 00=$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

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$10 00=$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

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$10 00=$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

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$10 00=$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

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$10 00=$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