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.
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))
Last edited by Søren Larsen; 05-21-2012 at 06:10 AM.
Sincerely
S?ren Larsen
"Give a man a fish, and you'll feed him for a day. Give a man a fishing rod, and he'll steal your yacht!"
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.
Try this in C2
or with 2007 and above
Please Login or Register to view this content.
Drag Across then down as required.Please Login or Register to view this content.
[EDIT]
Better to replace FIND with SEARCH in the above formulae. Find() is case-sensitive, SEARCH() isn't.
Last edited by Marcol; 05-21-2012 at 06:54 AM.
If you need any more information, please feel free to ask.
However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....
Also
اس کی مدد کرتا ہے اگر
شکریہ کہنے کے لئے سٹار کلک کریں
If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
Does Marcol's solution do the trick, or are you not there yet?
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.
Do you know how many headings you'll end up having?
Hi Stan,
No, I don't.
Kind Regards,
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.
Do you know how many headings you'll end up having?Hmmm?No, I don't.
Try this in Sheet "Current" C2
Drag Down as required, then Across until all rows return blanks.Please Login or Register to view this content.
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!
Seems a lot to me, however, you have them now ...It took me a while to get the number of headings. There are 1201 unique values that will become headings.
See this workbook.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks