• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data in Table format.

Hi Seniors,

I need some help with the attached Excel with 1st Sheet is Dump and 2nd sheet is Result.

1st Sheet contains a Raw dump which I need in proper format like 2nd sheet "Result" showing.

Whereas shared raw dump contains 500+ cell but actual raw dump having more than 4 Lakh cell.

Is it possible to change raw dump data with result table for the 4 Lakh above cells ??

Kindly help with the formula ?? Thanks in Advance.


Regards // Prem
 

Attachments

  • Analysis_1.xls
    37.5 KB · Views: 1
Hi !

Doable by code.

If source data come from external like a text file,
better is to attach a source file …​
 
Last edited:
Hi Marc,

Thanks for response. Yes the source data is text file but text file size is too heavy (12mb +). It is not possible to attach.
 

I was hoping text file may be better than worksheet data …
But what a weird text file format ‼

To avoid a gas factory code and a long processing time,
better is to parameter text file creation with a data separator
like tabulation or comma for example, avoiding space …
 
Hi Marc,

I know text file too much manipulated, that's why I mapped the data with my first attachment. I have tried with data separator too but it is not being done. I would thankful If you can help with excel file which i have shared earlier.
 

So it's the far slowest way ‼

You have many files like that to process ?
Columns order & titles are always the same whatever the source text file ?
 
A Dumper demonstration according to your attached Dump worksheet :​
Code:
Sub DemoDumper()
                        R& = 1:    L& = 1
With Sheet2
            With .Rows:  .Item("2:" & .Count).Clear:  End With
            Application.ScreenUpdating = False
    Do
        Select Case Left(Sheet1.Cells(R, 1).Value, 4)
        Case "CELL"
             L = L + 1:      C& = -7
             .Cells(L, 1).Value = Sheet1.Cells(R + 1, 1).Value
        Case "CHGR"
                              C = C + 9
             If .Cells(C).Value = "" Then .[B1:J1].Copy .Cells(C)
             .Cells(L, C).Value = Sheet1.Cells(R + 1, 1).Value
             .Cells(L, C).TextToColumns , xlFixedWidth, FieldInfo:=Array([{0,1}], [{7,1}], [{18,1}], [{34,1}], [{51,1}], [{60,1}])
        Case "    "
             .Cells(L, C + 6).Value = Sheet1.Cells(R + 1, 1).Value
             .Cells(L, C + 6).TextToColumns , xlFixedWidth, FieldInfo:=Array([{0,9}], [{7,1}], [{24,1}], [{32,1}])
        Case Else
             Exit Do
        End Select
                        R = R + 2
    Loop
            With .Cells(1).CurrentRegion:  .Columns.AutoFit:  .HorizontalAlignment = xlCenter:  End With
            .Activate
End With
End Sub
Do you like it ? So thanks to click on bottom right Like !
 

Any news ? So don't be surprised next time if you will be blacklisted ‼

I hope you understand my demonstration is just a sample
for the unnecessary raw dump worksheet, far not the best way
but enough for Dumb or Dumber ! …
 
Hi Marc,

I have tried your code. It is working good. I was not able to revert back yesterday because I had been out office before your reply. But when i logged today. Your response is totally surprising. Don't want to argue with you. But anyway thanks. It may be your mentality.
 
Not my mentality but often OP do not give news after they had a code …

How long my process takes with your real data ?
How do you import data in the dump worksheet ? How long ?

If the real text file is like the one attached in post #5,
it's possible to directly load data in result worksheet !

From my side, this kind of process with a 13meg text file,
around 5 Lakh lines and 29000 rows in worksheet result,
lasts less than 3 seconds ! (depends also on processor power, mine in middle …)
 
Last edited:
Back
Top