Easier way to copy and paste values into a single line?

Hi Boston dweller and welcome to the forum,

To give a better answer we should see a sample of what you mean. You can attach a sample using "Go Advanced" and then click on the Paper Clip Icon above the advanced message area. I think this might be an easy problem but what the data looks like is more of the problem to give you a good answer.

Alright so I attached this sample book.

The first sheet is supposed to show you how I just copy and paste everything from the database at first.

The second sheet is supposed to show you the duplicate timepoints and the values. Can you see what I mean? There are two lines with the same timepoint

for example
2/24/15 00:15 EST values 1 2 3 - - -
2/24/15 00:15 EST values - - - 4 5 6

How can I just get them to join into a single row without manually copying and pasting it into one line?

2/24/15 00:15 EST values 1 2 3 4 5 6

Hi,

See if this saves some time for you. On your Timepoints sorted sheet. I see that you know how to autofilter and sort by time.

Do this.....
Click in cell J4 and drag down to P75. This will select this whole block.
Then put the cursor on the top of the selected block and drag it up ONE Row.
Now all your data is on the same rows, instead of doing them one at a time. Then filter out all blank rows using any one of the column dropdowns.

A problem might occur if you don't have two times exactly the same.
In that case some VBA might be a better method.

Reply back after you have tried this manual method. If there might be missing times between the two sets of data then we'll find a better method.

If the above manual method

Ok - I have a better answer than the manual one above.

After seeing it was vital records and needed to decrease your BP doing this problem, here is the answer.

In J3 put this formula and pull it across and down
=VLOOKUP($A3,INDIRECT("$A"&ROW()+1&":$P" & COUNTA($A:$A)),COLUMN(),FALSE)

See the attached

So what does this fancy formula do?
It looks at the Time in Column A and then looks down the sheet for a matching time. If and when it finds a match to the time it returns the number in the same column the formula is in to that cell.

I think this is a better answer.

I'd then copy and paste VALUES ONLY over my formulas to set the numbers in those cells.

Watch out for that blank column between data sets. This will make the
COUNTA($A:$A) short a few for each blank line between the sets. You could add a few rows to be safe by making the formula...

=VLOOKUP($A3,INDIRECT("$A"&ROW()+1&":$P" & COUNTA($A:$A)+10),COLUMN(),FALSE)

I hope this brings your BP down and helps analyze data and save patients.

Alright so when I pull it across it looks like it works, but pulling it say all the way down to P75 gives me N/A. Do I have to go to each line and then just input the formula at J3 J5 J7 and just pull it across?

Yeah thanks, my BP is way too high from all this c+ping im doing haha.

Another thing is, there are a lot of instances in which there is a rogue timepoint that doesn't have a duplicate, so the above manual method would not work (taking from J4 and highlighting to P75 then dragging it a row above)

This is why we need to see the real data.

First you only pull the formula down for the TOP set of your data. Then Copy and past VALUES ONLY over the top of the formulas. Then you can delete the rows for the bottom set of data.

If the times don't match exactly then we might need to do a MRound function to get them all within the same 15 second (whatever makes sense) range. After this MRound then the timestamps between machines problem might go away.

See if this helps... If not show me where the problems are and we can drop your BP more.

Here is how to do what I think you want...

On your template sheet you have a blank row at row 27. Delete this whole row to make your data a full table.

Then select all your data from A1 to PK54 and Ctrl-C (Copy it)

Go to sheet 3 and click on cell A1. Right click on A1 and Paste Special. Check the "Transpose" checkbox and then OK.

This will get what you want - with a few changes needed on the top of the sheet.