Data Transformation Required

[theabdulrab]

Hi All,

Attached spreadsheet contains two tabs "Current" & "Required". "Current" contains the data that needs to be converted into the format present in "Required" Tab.

I have a huge file, 127MB, with 0.2 million rows of such data & I really need help in getting the data transformed.

Thank you.

Kind Regards,
theabdulrab.

[Søren Larsen]

I would use the "Text to Columns" option on your original data, using "|" as delimiter. and then in C2 of "Required":

=MID(Current!B2,FIND("=",Current!B2)+1,256) and copy down and across.

EDIT: Since the above will return both text and numbers as text, then this might be better:

=IFERROR(MID(Current!B2,FIND("=",Current!B2)+1,256)*1,MID(Current!B2,FIND("=",Current!B2)+1,256))

[theabdulrab]

Hello Søren Larsen,

Thank you for your reply.

The column headers needs to be picked up within the data present in Cell B2 and B3 of "Current" tab & then the data should be populated as presented in "Required" tab. In other words, I don't have all the column headers listed and I will have to pick them up manually from the cell B2, B3 ........

Thanks again for your help.

Kind Regards,
theabdulrab.

[Marcol]

Try this in C2

Please Login or Register  to view this content.

or with 2007 and above

Please Login or Register  to view this content.

Drag Across then down as required.

[EDIT]
Better to replace FIND with SEARCH in the above formulae. Find() is case-sensitive, SEARCH() isn't.

[Søren Larsen]

Does Marcol's solution do the trick, or are you not there yet?

[theabdulrab]

Hi,

Thanks for your replies.

I believe I am not clarifying my requirements clearly. So, I am giving it an other shot:

The data that I currently have is present in the tab "Current". What I need to do is that I want to pick the value before "=" and make it as a column heading in the next tab. The value after the "=" should come under respective column heading. All column headings are delimited by "|".

I manually wrote the column heading in "Required" tab just to give an idea and I want this done thru a macro or VBA code.

One more thing is Cell B2 & B3 of "Current" tab contains different set of Column Headings, so, the tab "Required" should have a union of all the headings present in cell B2 & B3 of "Current" tab.

Hope this clarifies my problem.

Kind Regards,
theabdulrab.

[Søren Larsen]

Do you know how many headings you'll end up having?

[theabdulrab]

Hi Stan,

No, I don't.

Kind Regards,
theabdulrab.

[theabdulrab]

Hi Stan,

It took me a while to get the number of headings. There are 1201 unique values that will become headings.

Kind Regards,
theabdulrab.

[Marcol]

Do you know how many headings you'll end up having?

No, I don't.

Hmmm?
Try this in Sheet "Current" C2

Please Login or Register  to view this content.

Drag Down as required, then Across until all rows return blanks.

This will extract all of the headers you will need, but there will be duplicates.

You can then Copy and Paste Special > Values to a suitable location, transpose these rows to columns, transfer all that into a single column, remove duplicates, then transpose all of that into your header row, and then use the formula I gave you in post #4

Personally I'd rather have broken bottles for breakfast!

It took me a while to get the number of headings. There are 1201 unique values that will become headings.

Seems a lot to me, however, you have them now ...

See this workbook.