How to calculate multiple values referenced to a single value for multiple data entries

[SomeCallMeGenius]

Hello all,

I am new to this forum, and don't know much about excel. Here is what I am trying to do: -


A B C D E F G H I
1 Name G 1 2 3 4 5 6 7
2 Roger C C G G C C C
3 Kristie G G C C C C C
4 Paul C C C C G C C
5 Joe C C C C C C G

So column A2:A5 has some names, Row C1:I1 has dates, on each specific date, all users are assigned specific tasks (either C or G). What I want to do here is is to fill each cell in column B2:B5 with the dates on which a particular user has to do task "G".

Like B2 should have the value 3,4. B3 - 1,2. B4 - 5 and B5 - 7.

I tried using Vlookup but it only returns the first date. I looked up on net and found an array formula, couldn't quite get it working with my data. Any suggestion or help would be appreciated. Thanks all.

Regards,
SG

 

[SomeCallMeGenius]

So data looks all messy and can't quite get html maker, not sure how to put it in a better way in this forum.

 

[SomeCallMeGenius]

so did some more research, found a UDF for vlookup - "vlookupnth", not able to get it working either. Help needed guys, please suggest.



Thanks.

 

[SomeCallMeGenius]

Please guys, still need some help. If this is something not possible, let me know. Or if it require some UDF, please let me know.

Thanks.
-SG

 

[SomeCallMeGenius]

no help

here is the data in a better look: -


http://img641.imageshack.us/img641/2200/imagedat.jpg

Thanks

 

[SomeCallMeGenius]

bump.

 

[pedie]

SomeCallMeGeniusI am not sure what you are trying to achieve....

Put things precise and clear....

 

[jasonb75]

If you don't have it already, download and install the MOREFUNC add-in.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Name	G	1	2	3	4	5	6	7
2	Roger	3,4,	C	C	G	G	C	C	C
3	Kristie	1,2,	G	G	C	C	C	C	C
4	Paul	5,	C	C	C	C	G	C	C
5	Joe	7,	C	C	C	C	C	C	G
Sheet

 

[Peter_SSs]

You could try this macro in a copy of your workbook. Post back if you need help with how to implement it.

It assumes the 'Task of Interest' is in cell B1 and the main data starts in cell C1. the result of running the code on your data (with a few more rows added by me) is shown below.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Tasks()<br>    <SPAN style="color:#00007F">Dim</SPAN> aData<br>    <SPAN style="color:#00007F">Dim</SPAN> Results() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lc <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, task <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>    lr = Cells(Rows.Count, 1).End(xlUp).Row<br>    lc = Cells(1, Columns.Count).End(xlToLeft).Column<br>    aData = Range("C1", Cells(lr, lc)).Value<br>    <SPAN style="color:#00007F">ReDim</SPAN> Results(1 <SPAN style="color:#00007F">To</SPAN> lr - 1)<br>    task = Range("B1").Value<br>    <SPAN style="color:#00007F">For</SPAN> r = 2 <SPAN style="color:#00007F">To</SPAN> lr<br>        s = ""<br>        <SPAN style="color:#00007F">For</SPAN> c = 1 <SPAN style="color:#00007F">To</SPAN> lc - 2<br>            <SPAN style="color:#00007F">If</SPAN> aData(r, c) = task <SPAN style="color:#00007F">Then</SPAN><br>                s = s & "," & aData(1, c)<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> c<br>        Results(r - 1) = Replace(s, ",", "", 1, 1)<br>    <SPAN style="color:#00007F">Next</SPAN> r<br>    Range("B2").Resize(UBound(aData, 1) - 1).Value = Application.Transpose(Results)<br>    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Name	G	1	2	3	4	5	6	7
2	Roger	3,4	C	C	G	G	C	C	C
3	Kristie	1,2	G	G	C	C	C	C	C
4	Paul	5	C	C	C	C	G	C	C
5	Joe	7	C	C	C	C	C	C	G
6	Sam	1,2,3,4,5,6,7	G	G	G	G	G	G	G
7	Tim		C	C	C	C	C	C	C
8	Ted	2,4,6	X	G		G		G	Y
9
Sheet8

 

[SomeCallMeGenius]

Thanks all for the help