Arranging Data in proper way

[uday]

Hi,

I have a huge data in comma separated format in text file. When I am trying to text to column that data in excel workbook it is not giving a proper outlook the way I want it.

I have created a dummy version of workbook to represent my issue. In attached file comprises of 7 rows where highlighted data sets in yellow color starting from different column. Taking example of Row 4, 5 and 7. All the data sets have the 6 digit alphanumeric number followed by other information. They all needs to be fit and start from column R which is dedicated for Product number only.

I have already created a macro which is also embedded in same file. You can run it see my expected result. The issue is - the unorganized (Row # 4,5 and 7) data sets are not fixed or starting from a specific column. The data sets which have been highlighted in yellow can appear in any column starting from 1 to 37. The pros is the data sets which are not coming in right way always starts from alphanumeric data.

Please assist me to put all the data which are not coming in right format, they should all start from column R which is product number.

Regards,

Roy

 

[vletm]

uday
With those criteria, this would do it.
Press Your [ Macro ]-button.

 

[Marc L]

Hi !

As a beginner starter :​

Sub Demo1()
            Dim Rg As Range
    With ActiveSheet.UsedRange.Columns(1)
        If Application.CountBlank(.Cells) Then
            For Each Rg In .SpecialCells(xlCellTypeBlanks)
                Range(Rg.End(xlToRight), Rg.End(xlToRight).End(xlToRight)).Cut .Cells(Rg.Row, 18)
            Next
        End If
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[uday]

Thanks!!!!! vletm.

It is working like a charm as I wanted. However can I expect the same result if anything put in front of yellow highlighted data set? I have put some data and highlighted them with Orange color. File attached.

Regards,
Uday

 

[vletm]

uday - no!
As I wrote: With those criteria, this would do it.
This 'sample file' is different!
Is 'Product Number'-cell ONLY and ONLY which length 6 and
as 'number number LETTER LETTER number number'?
... ONLY means ONLY!
Would those 'oranges' stay or move somewhere? or no matter?

 

[Marc L]

can I expect the same result if anything put in front of yellow highlighted data set? I have put some data and highlighted them with Orange color.

Doable via the areas returned by the SpecialCells method
for non blank cells …
​

 

[vletm]

uday
No new criteria nor answer ... okay
... it would be doable eg this way ...

 

[uday]

Thanks for your assistance vletm... But I am wondering why the orange data also moving along with yellow data. Is it possible to fixed the position of orange data keep it in same place?

If I am putting my thought in merry-go-round, then I beg sorry for it.

Regards,

Roy

 

[vletm]

uday
If You would write what would You would like to get in the beginning
and
If You would read question and after that answer,
then many things would be different.
(I asked three questions with #5 Reply...)
Ps Do those colors would keep same position, follow moved parts or no matter?

 

[uday]

I understand!

I am start answering the post # 5

As I wrote: With those criteria, this would do it.
This 'sample file' is different! - Yes, I forgot to put my second condition in first place. Sorry!
Is 'Product Number'-cell ONLY and ONLY which length 6 and
as 'number number LETTER LETTER number number'?
... ONLY means ONLY! - Yes, It will be in same pattern.

Would those 'oranges' stay or move somewhere? or no matter - Oranges should stay in same place as I only want to move the yellow data.

Regards,

Roy

 

[vletm]

uday
Hint: Use term 'just remember' instead 'forgot' ... that would sound better.

 

[uday]

....Ok Sir....

 

[Marc L]

uday, better is to post your real & complete need
when creating the original post instead of waiting the first solution
to change the question or the context …

Doable via the areas returned by the SpecialCells method
for non blank cells …

As I wrote just with the easy areas way :​

Sub Demo2()
            Dim R&, Ar As Areas
    With Sheet1.Cells(1).CurrentRegion.Columns("A:Q").Rows
        For R = 2 To .Count
            If Application.CountA(.Item(R)) Then
                Set Ar = .Item(R).SpecialCells(xlCellTypeConstants).Areas
                If Val(Ar(Ar.Count)(1).Value2) Then Ar(Ar.Count).Cut .Cells(R, 18)
            End If
        Next
    End With
                Set Ar = Nothing
End Sub

Do you like it ? So thanks to click on bottom right Like !
​