|
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 |
find multiple data cells for one data source
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 |
find multiple data cells for one data source
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 |
find multiple data cells for one data source
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 |
find multiple data cells for one data source
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 |
find multiple data cells for one data source
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 |
find multiple data cells for one data source
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 |
find multiple data cells for one data source
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 |
find multiple data cells for one data source
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 |
| All times are GMT +1. The time now is 11:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com