Transposing a column hundreds of times without doing it manually

Hello all,

I have a question as to how I can transpose lots of data that's stacked ontop of each other, into rows that fit into their general categories. In the attached below, I have column A, and Column B. I want the data to be transposed by "looking up" if you will off column A, than posting the B value into its transposed area. Without manually changing the Array each time, I can't get this to work with a Vlookup, I was wondering if there was another way via maybe a conditional index, or something that would work? Any help is appreciated.

Thanks,

Math

Copy of AWP corrugated specs.xlsm

Hey FDibbins,

I appreciate the help, but since the columns aren't all the same length that doesn't seem to work. If you try it on the sheet I attached above, it just seems completely scrambled and not in order at all.

Thanks,

Math

Hey Marvin,

Yes the words in column A are in the same order, most are exactly the same, the real issue is sometimes documents have been edited so they're "revised and revise" which adds 2 rows in between that aren't usually there. That being said, in my first post I alluded to some way attaching the word in column A, like a V Lookup transposed into the rows. The problem with V lookups is I would have to change the Array for each row in order for it to work, which would be just as tedious as copying and than transposing each document.

Thanks,

Math

Hey Marvin,

I am not sure what you mean by data in Column C. There is data in the first few rows in that spreadsheet which is an example of what i want it to look like. There will be mutiple of the same words in column A since it's just a massive list of documents. For example, these are the words that need to be matched and transposed and looked up for the parrell column "B" item

Please Login or Register  to view this content.

So, all of those get repeated hundreds of times, as that is the template for one of our documents, when I pulled the documents out of the source system it looks like you see on the screen, just 2 columns of massive data. I'd like that to be transposed in a nice fashion as you can see the first 6 or 7 rows. Spaces are acceptable as well if they need to stay there. Does that make sense?

Thanks,

Math

Hey FDibbins,

As you can see in your attached, the yellow is what I want to get to. The column headers for the yellow correspond directly with the verticals in column A, only repeated over and over again, because each document starts at "OrginalModTime" and ends at "Graphics".

Basically I just want something that looks like the yellow, by transposing column B using smart matching on column A. Does that make sense?

And unfortunetly there's no other way to pull the data...it's out of a tool called "Lotus Notes" which is older then myself =\

Thanks,

Math

Ah I see the issue.

The ones that are in the yellow column are already done, so we deleted the data. I can see how that could cause much confusion. Does that make sense? There won't be any of the data below, as that's all stuffed we haven't done yet.

Thanks,

Math

Hey FDibbins,

Sorry on the delayed response.

I just tried the updated forumla and am running into the same problem, it shows the first line fine (Which is great!) but it won't show subsequent lines, so it's basically acting moreas a vlookup than anything...The only thing that seems to update is the history column. Do you know why this could be? I see its pulling in stuff from the C and D column which can just be ignored, I only need stuff from the B column.

Thanks,

Math

Am I allowed to bump this?