gather 6 columns based on row

[jackf-nc]

I have a excel sheet that has 2 pages.
Sheet1 W76 has a date
Sheet 2 is situated like: column a = date, column d:i are 6 numbers (that I need)

I want to place these #'s - values (Sheet 2- D:I)on sheet 1 in a single cell( with comma and space) in v76, if date from sheet 1 matches sheet2 (A:A)

Any ideas...?

Thanks,Jack

[Special-K]

Will there ALWAYS be 6 numbers, ie are ALL the values in columns D:I filled in?
If not you're gonna end up with something like 1, 2, , 4, 5, 6 or 13, 27, 71, , , which you're bound to say you dont want., hence the question.

[jackf-nc]

there will ALWAYS be # based off date on sheet 1 w76
thanks for your assistance!

[jackf-nc]

Sheet 2 never contains duplicate dates........Dont know if this helps?

[FDibbins]

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[jackf-nc]

Will there ALWAYS be 6 numbers, ie are ALL the values in columns D:I filled in?
If not you're gonna end up with something like 1, 2, , 4, 5, 6 or 13, 27, 71, , , which you're bound to say you dont want., hence the question.

Yes data will always be filled in for D:I if a: contains date.....

[José Augusto]

Try this with a helper column in Sheet2 (I use the column J)

Please Login or Register  to view this content.

After that use the following formula in Sheet1!W76

Please Login or Register  to view this content.

[jason.b75]

Yes data will always be filled in for D:I if a: contains date.....

A sample would help establish the most suitable method.

=TEXT(SUMPRODUCT(INDEX(Sheet2!D:I,MATCH(Sheet1!V76,Sheet2!A:A,0),0)*10^{5,4,3,2,1,0}),"0\,0\,0\,0\,0\,0")

Would work with single digit integers, without the need for the extra column that Jose has used, but is not suitable for decimals or an unknown number of digits.

[jackf-nc]

Thank you so much guys.... I was unable to get either solution to work. I have made a dummy sheet for you to see what I am trying to do. Thanks again for your assistance.

[jason.b75]

My suggestion is no good for your example, but Jose's works fine.

Enter the first formula from post #7 into J1 on Sheet2, then copy it down to the other rows.

Enter the second formula into V76 of sheet1, note that Jose had a typo in the second formula, Sheet1!V76 should actually be Sheet1!W76

[jackf-nc]

I think i got part of it to work, using Jose's method - instead of using helper on sheet 2 I simply created array over existing data. Do I really need a helper on sheet 2?

=INDEX(Sheet2!D1:I500,MATCH(W$76,Sheet2!A1:A500,0),1)

It does display first cell - D4(25), but how do i share complete array (25,48, 51, 65, 72, 4) - actually thats how i want it shown in cell w76 on sheet one. Am i going to to have create a separate index for each number?
Thanks again...

[jackf-nc]

I can get all to work using:

Please Login or Register  to view this content.

But it seems that there is a cleaner method, than this? Is there? Any suggestions?