Hi,
I have a data which looks like this with headers where the columns variable.
423115000001 TYPE -- -- MATERIAL COTTON COTN WEIGHT 100 GM 100GM DIMENSION -- -- ADDITIONAL INFORMATION -- --
I am trying to get the output like below
423115000001 TYPE -- --
423115000001 MATERIAL COTTON COTN
423115000001 WEIGHT 100 GM 100GM
423115000001 DIMENSION -- --
423115000001 ADDITIONAL INFORMATION -- --
any help would be great i have more than 30000 rows to be converted in this pattern.
Hello sonkar,
To expedite a solution, you should post a copy of your workbook. This will allow all code to be tested against the actual data and layout.
If your workbook contains sensistive information, please redact it before posting.
How To Post Your Workbook
- At the bottom right of the Reply window, Click the button Go Advanced
- At the top of the Your Message Window, in the first row of icons, Click the Paperclip icon.
- Click the Add Files button at the top right in the dialog box. This displays the File Manager dialog.
- Click the Select Files button at the bottom middle of the dialog.
- In the new window Find the file you want to upload, select it, and Click Open.
- You will now be back in the File Manager dialog. Click the bottom Middle button Upload File.
- Wait until the file has completely uploaded before you exit the File Manager dialog.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Hi,
Sorry for the late reply,I am attaching excel file where the input and outputs are in different sheets.
Hope that helps you to understand my problem.
Thanks
Sachin
Hello sonkar,
Thanks for posting the workbook. After looking at the data, I have a couple of questions.
On the "Input Data" sheet, are all the grey cells to the right of column "N" to be considered "Additional Information" ?
Will the number of columns vary only after column "N" ?
Hi..
Try this..
You had 2 'hidden' columns (B and C) on your Output sheet..
While i can see how you would get your second and third values .. example (Material and Weight).. it is not clear how you get the "ABSORBENT" value (first value)..
For this reason.. I have included only the 4 visible columns you showed in your output sheet..
You can either insert the other 2 columns and use a formula or vba to get the values..
You could also include those values in the building of the 'y' array.. I didn't for the above reason..
Click the button.. result shown on sheet1.
Please Login or Register to view this content.
Hi,
the columns are in the rage as long as header data till "AK"
I got the solution by using the formula
=INDIRECT("'Sheet1'!A"&TRUNC(ROW()/12)+1)
=INDIRECT("'Sheet1'!R"&TRUNC(ROW()/12)+1&"C"&(COUNTIF($A1:$A12,$A12)-1)*3+COLUMN(),FALSE)
Thanks a lot
will also try the macro and post if it works for me
Sachin
Hi APO,
The code works great
Thanks
Sachin
You're welcome..
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks