Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Look up values and place in spreadsheet
I have an excel workbook with two sheets.
On Sheet2, in column A is employee name. In column B is comments about that employee. The employee's name could be repeated several times throughout the workbook. Sheet2 has 1000 rows with multiple employees on it. On Sheet1 I want to put all the comments that match Employee1 (which is in cell A1 on Sheet1). There will probably be 20 matches, so I want the comments to be in rows 2-21. Is this possible? Thanks in advance. |
#2
|
|||
|
|||
Try this *array* formula in A2 of Sheet1:
=INDEX(Sheet2!$B$1:$B$30,SMALL(IF(Sheet2!$A$1:$A$3 0=$A$1,ROW($A$1:$A$30)),RO W(A1))) And drag down to copy. -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. When this formula runs out of matches, it returns a #NUM! error. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "jh3016" wrote in message ... I have an excel workbook with two sheets. On Sheet2, in column A is employee name. In column B is comments about that employee. The employee's name could be repeated several times throughout the workbook. Sheet2 has 1000 rows with multiple employees on it. On Sheet1 I want to put all the comments that match Employee1 (which is in cell A1 on Sheet1). There will probably be 20 matches, so I want the comments to be in rows 2-21. Is this possible? Thanks in advance. |
#3
|
|||
|
|||
This worked like a charm! Now how can I not display the #NUM! error. I
tried conditional formatting the column, but that didn't work. Thanks a million. "RagDyeR" wrote: Try this *array* formula in A2 of Sheet1: =INDEX(Sheet2!$B$1:$B$30,SMALL(IF(Sheet2!$A$1:$A$3 0=$A$1,ROW($A$1:$A$30)),RO W(A1))) And drag down to copy. -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. When this formula runs out of matches, it returns a #NUM! error. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "jh3016" wrote in message ... I have an excel workbook with two sheets. On Sheet2, in column A is employee name. In column B is comments about that employee. The employee's name could be repeated several times throughout the workbook. Sheet2 has 1000 rows with multiple employees on it. On Sheet1 I want to put all the comments that match Employee1 (which is in cell A1 on Sheet1). There will probably be 20 matches, so I want the comments to be in rows 2-21. Is this possible? Thanks in advance. |
#4
|
|||
|
|||
I was hoping you wouldn't ask for that.<bg
Makes for a big formula: =IF(ISERR(SMALL(IF(Sheet2!$A$1:$A$30=$A$1,ROW($A$1 :$A$30)),ROW(A1))),"",INDE X(Sheet2!$B$1:$B$30,SMALL(IF(Sheet2!$A$1:$A$30=$A$ 1,ROW($A$1:$A$30)),ROW(A1) ))) Watch out for word wrap! -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "jh3016" wrote in message ... This worked like a charm! Now how can I not display the #NUM! error. I tried conditional formatting the column, but that didn't work. Thanks a million. "RagDyeR" wrote: Try this *array* formula in A2 of Sheet1: =INDEX(Sheet2!$B$1:$B$30,SMALL(IF(Sheet2!$A$1:$A$3 0=$A$1,ROW($A$1:$A$30)),RO W(A1))) And drag down to copy. -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. When this formula runs out of matches, it returns a #NUM! error. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "jh3016" wrote in message ... I have an excel workbook with two sheets. On Sheet2, in column A is employee name. In column B is comments about that employee. The employee's name could be repeated several times throughout the workbook. Sheet2 has 1000 rows with multiple employees on it. On Sheet1 I want to put all the comments that match Employee1 (which is in cell A1 on Sheet1). There will probably be 20 matches, so I want the comments to be in rows 2-21. Is this possible? Thanks in advance. |
#5
|
|||
|
|||
This is absolutely wonderful. You have made my day.
Question -- Why function does "SMALL" do? "RagDyeR" wrote: I was hoping you wouldn't ask for that.<bg Makes for a big formula: =IF(ISERR(SMALL(IF(Sheet2!$A$1:$A$30=$A$1,ROW($A$1 :$A$30)),ROW(A1))),"",INDE X(Sheet2!$B$1:$B$30,SMALL(IF(Sheet2!$A$1:$A$30=$A$ 1,ROW($A$1:$A$30)),ROW(A1) ))) Watch out for word wrap! -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "jh3016" wrote in message ... This worked like a charm! Now how can I not display the #NUM! error. I tried conditional formatting the column, but that didn't work. Thanks a million. "RagDyeR" wrote: Try this *array* formula in A2 of Sheet1: =INDEX(Sheet2!$B$1:$B$30,SMALL(IF(Sheet2!$A$1:$A$3 0=$A$1,ROW($A$1:$A$30)),RO W(A1))) And drag down to copy. -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. When this formula runs out of matches, it returns a #NUM! error. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "jh3016" wrote in message ... I have an excel workbook with two sheets. On Sheet2, in column A is employee name. In column B is comments about that employee. The employee's name could be repeated several times throughout the workbook. Sheet2 has 1000 rows with multiple employees on it. On Sheet1 I want to put all the comments that match Employee1 (which is in cell A1 on Sheet1). There will probably be 20 matches, so I want the comments to be in rows 2-21. Is this possible? Thanks in advance. |
#6
|
|||
|
|||
Another question....
How do I count this? If Sheet2 A2:A400 = "Orange", count how many times "Orange" appears. "jh3016" wrote: I have an excel workbook with two sheets. On Sheet2, in column A is employee name. In column B is comments about that employee. The employee's name could be repeated several times throughout the workbook. Sheet2 has 1000 rows with multiple employees on it. On Sheet1 I want to put all the comments that match Employee1 (which is in cell A1 on Sheet1). There will probably be 20 matches, so I want the comments to be in rows 2-21. Is this possible? Thanks in advance. |
#7
|
|||
|
|||
=countif(a2:a400,"orange")
or =countif(sheet2!a2:a400,"orange") jh3016 wrote: Another question.... How do I count this? If Sheet2 A2:A400 = "Orange", count how many times "Orange" appears. "jh3016" wrote: I have an excel workbook with two sheets. On Sheet2, in column A is employee name. In column B is comments about that employee. The employee's name could be repeated several times throughout the workbook. Sheet2 has 1000 rows with multiple employees on it. On Sheet1 I want to put all the comments that match Employee1 (which is in cell A1 on Sheet1). There will probably be 20 matches, so I want the comments to be in rows 2-21. Is this possible? Thanks in advance. -- Dave Peterson |
#8
|
|||
|
|||
Now I have another problem. The comments are cutting off at a certain point.
Like, only 500 or so characters are coming over in the spreadsheet. This is as a result of the =IF(ISERR... formula described previously. Is there a way that I can capture ALL of the comments? Thanks. "jh3016" wrote: I have an excel workbook with two sheets. On Sheet2, in column A is employee name. In column B is comments about that employee. The employee's name could be repeated several times throughout the workbook. Sheet2 has 1000 rows with multiple employees on it. On Sheet1 I want to put all the comments that match Employee1 (which is in cell A1 on Sheet1). There will probably be 20 matches, so I want the comments to be in rows 2-21. Is this possible? Thanks in advance. |
#9
|
|||
|
|||
I don't quite understand.
Do you mean that you have, for example, a *single* cell on Sheet2 that contains approx. 1,000 characters, and when the content of this cell is returned to Sheet1 by the formula, not all the characters are displayed? If so, check out the actual length of cell in Sheet2 with this formula, where you put the cell address in the parenthesis: =LEN(cell address) And, do the same for the cell on Sheet1, and post back -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "jh3016" wrote in message ... Now I have another problem. The comments are cutting off at a certain point. Like, only 500 or so characters are coming over in the spreadsheet. This is as a result of the =IF(ISERR... formula described previously. Is there a way that I can capture ALL of the comments? Thanks. "jh3016" wrote: I have an excel workbook with two sheets. On Sheet2, in column A is employee name. In column B is comments about that employee. The employee's name could be repeated several times throughout the workbook. Sheet2 has 1000 rows with multiple employees on it. On Sheet1 I want to put all the comments that match Employee1 (which is in cell A1 on Sheet1). There will probably be 20 matches, so I want the comments to be in rows 2-21. Is this possible? Thanks in advance. |
#10
|
|||
|
|||
Yes, there are 1324 characters in that single cell.
"RagDyer" wrote: I don't quite understand. Do you mean that you have, for example, a *single* cell on Sheet2 that contains approx. 1,000 characters, and when the content of this cell is returned to Sheet1 by the formula, not all the characters are displayed? If so, check out the actual length of cell in Sheet2 with this formula, where you put the cell address in the parenthesis: =LEN(cell address) And, do the same for the cell on Sheet1, and post back -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "jh3016" wrote in message ... Now I have another problem. The comments are cutting off at a certain point. Like, only 500 or so characters are coming over in the spreadsheet. This is as a result of the =IF(ISERR... formula described previously. Is there a way that I can capture ALL of the comments? Thanks. "jh3016" wrote: I have an excel workbook with two sheets. On Sheet2, in column A is employee name. In column B is comments about that employee. The employee's name could be repeated several times throughout the workbook. Sheet2 has 1000 rows with multiple employees on it. On Sheet1 I want to put all the comments that match Employee1 (which is in cell A1 on Sheet1). There will probably be 20 matches, so I want the comments to be in rows 2-21. Is this possible? Thanks in advance. |
#11
|
|||
|
|||
Was that in Sheet2, or Sheet1, or both?
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "jh3016" wrote in message ... Yes, there are 1324 characters in that single cell. "RagDyer" wrote: I don't quite understand. Do you mean that you have, for example, a *single* cell on Sheet2 that contains approx. 1,000 characters, and when the content of this cell is returned to Sheet1 by the formula, not all the characters are displayed? If so, check out the actual length of cell in Sheet2 with this formula, where you put the cell address in the parenthesis: =LEN(cell address) And, do the same for the cell on Sheet1, and post back -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "jh3016" wrote in message ... Now I have another problem. The comments are cutting off at a certain point. Like, only 500 or so characters are coming over in the spreadsheet. This is as a result of the =IF(ISERR... formula described previously. Is there a way that I can capture ALL of the comments? Thanks. "jh3016" wrote: I have an excel workbook with two sheets. On Sheet2, in column A is employee name. In column B is comments about that employee. The employee's name could be repeated several times throughout the workbook. Sheet2 has 1000 rows with multiple employees on it. On Sheet1 I want to put all the comments that match Employee1 (which is in cell A1 on Sheet1). There will probably be 20 matches, so I want the comments to be in rows 2-21. Is this possible? Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
place empty cell with zero | Excel Worksheet Functions | |||
Get top 3 values from a list | Excel Worksheet Functions | |||
How to use same formula throughout the spreadsheet?? | New Users to Excel | |||
Pivot Table - using Countif in place of Count,Sum, etc. | Excel Discussion (Misc queries) | |||
Pick values from a row | Excel Worksheet Functions |