Transposing a vertical list automatically after pasting it in a column?

Good morning gurus;

I've got some PDF files (all the same format) that are converted to one big CSV. Two columns: ColA = field names, ColB = values. Just a few values for each person (8 values actually).

And, I've built into the PDF a way for it to produce a field that says "linebreak" between people so I can spot them.

I know how to paste in transpose, but I want to be able to paste all of these two columns into the sheet and have it formulaically transpose them so each person's "situation" is listed horizontally.

I can't seem seem to figure out how to get excel to recognize that after the word linebreak, the next 8 lines should be transposed into a single line.

This time I actually have to attach what I'm talking about (see attached sheet).

As always, thank you.

That worked well for the first person but it replicates the first person on every line when you drag downward. Just a whole lotta of George Washingtons

Originally Posted by JohnTopley

See amended post #2

I'll check that. I as soon as I posted I saw you recognized it too.

Originally Posted by JohnTopley

See amended post #2

You sir, are underpaid.

Thank you.

Originally Posted by JohnTopley

in C2

=IFERROR(INDEX($B$2:$B$18,AGGREGATE(15,6,(ROW($A$2:$A$1000)-ROW($A$2)+1)/($A$2:$A$1000=C$1),ROWS($1:1))),"")

Copy across and down

John, could you take a moment and break that down for me? I'm re-pasting more data in (that's got an extra row now) and I thought I understood what you made, but apparently not.

See, I'm exporting from acrobat dc pro. PDF -> Excel.

The form had a title image and text in the center of the header area. It comes into excel as just text, but I'm trying to ignore it. I feel like if you explain the formula I can make an adjustment based on that.

So,

I've got a fillable form that folks will send in to me (just a few fields).

Acrobat DC pro combines them to a single pdf (for ease) and exports to CSV--which seems to work.

The aggregate function seems to not recognize the last name: field to start scrutinizing even though everything is formatted as text.

I've included a sample here.

SO IT DID.

Good catch.

I just realized too that all of those files had the same data. I'm having some trouble with acrobat combining the 4 files and not just repeating the first one 4 times.

The formula is a little simpler

Formula:

Please Login or Register  to view this content.

Originally Posted by JohnTopley

All your heading in row 1 have a trailing blank e.g. "Last Name: " rather than "Last Name:" so matching fails.

I fixed the trailing blanks and duplicate pdf, but it's only capturing the data of the first 2 forms. The presentation of the other two forms is identical (in terms of labels and such) because they all came from the same fillable form originally.

Disregard, it was the B:b index range. Needed to expand that.

I will, just need to test a tad more before reverting it back to solved.