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.

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.

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.

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.

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.

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.

=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

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.

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.

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.

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.