Reordering data and combining with another spreadsheet

1. It would help to see the workbook.

You get better help on your question if you add a small excel file, without confidential information.

Please also add manualy the expected result in your file.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

Thanks,

See attached. Hopefully this should make more sense.

Of note, the numbers of results for each subject in DB2 is variable and the dates between each test may also be variable. The important thing is that the results are laid out sequentially. If the percentage change is difficult due to the variability in number of results, it should be between the first and second result

Many thanks for the assistance!

Paul

Helpcolumn in the sheet data a2=B2&countif($B$2:$B2,B2)

in sheet result: b10 =iferror(Vlookup($A10&column()-1,data!$A$2:$C$23,3,0),"") and drag accross.

see the attached file.

Please Login or Register  to view this content.

we split this formula in:

1) iferror(the formula,"") if the formula give a failure as result it will show a blanc cell.

this formula makes that cell F10 is blanc

2) Vlookup($A10&column()-1,data!$A$2:$C$23,3,0)

Vlookup
find Subject A1
in the range on sheet 2 (which is data) in A2:C23
in column 3 (the 3 in the formula)
find the exact match (1 is the other option)

3) b10=$A10&column()-1

is subject1 and add since the formula is in column B (is value 2) and we need the result 1. we abstract het result with 1 so we get 2 -/- 1 = 1.
the result from this aktion is SubjectA1
I use this so we can copy the formula tot the right and below.

Hope I explained well enough.

One question, do I have to manually create the helpcolumn or is this automated - I have >3000 rows to do!

copy the first value and paste this formula to row 2 till 3000 (so you can do this automatic).

I've used the formula =B2&COUNTIF($B$2:$B2,B2) on the data worksheet (ordered by name then date) and this has created sequential numbering for each unique subject in column A

Iff you add the dates in your formula, you also have to add the date in the Vlookupformula.

It makes the result unique.




=IFERROR(VLOOKUP($A1&COLUMN()-1,data!$A$2:$C$23,3,0),"")

That formula is on a other worksheet (see the example).


Otherwise please post a small example.

Otherwise please post a small example.

show in the file where the date has to be incoperated.