Look up values and place in spreadsheet

[Guest]

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.

 

[RagDyeR]

Try this *array* formula in A2 of Sheet1:

=INDEX(Sheet2!$B$1:$B$30,SMALL(IF(Sheet2!$A$1:$A$30=$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!
==============================================


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.

 

[Guest]

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]

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!
=====================================================

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.

 

[Guest]

This is absolutely wonderful. You have made my day.

Question -- Why function does "SMALL" do?

 

[Guest]

Another question....

How do I count this?

If Sheet2 A2:A400 = "Orange", count how many times "Orange" appears.

 

[Dave Peterson]

=countif(a2:a400,"orange")
or
=countif(sheet2!a2:a400,"orange")

 

[Guest]

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.

 

[RagDyer]

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

 

[Guest]

Yes, there are 1324 characters in that single cell.

 

[RagDyer]

Was that in Sheet2, or Sheet1, or both?