[SOLVED] Extracting Data Located in Single Cell

apologise.. i have just realised that.
It should be there now.

Thanks for the prompt reply.

Hi Kyle123,

You nailed it. Except they are being duplicated.

Would you mind applying the same principle to this second file attached. For Sheet 3. I am trying to analyse your code, hoping I can replicate this in the future.

Thank you ever so much.

Hi,

They are duplicated as you have your example below, it is returning these names also on the second sheet. I'd assumed that you wouldn't have the example of how you'd like the data to look on the actual document. Let me know if I got this wrong and I'll have a look

I'll have a look at your other workbook

Hi Kyle,

If possible I only want the organised data to appear on the second sheet.

I so envy people like you.. i wish i can be good with VBA too

Thanks once again.

Originally Posted by Kyle123

Hi,

They are duplicated as you have your example below, it is returning these names also on the second sheet. I'd assumed that you wouldn't have the example of how you'd like the data to look on the actual document. Let me know if I got this wrong and I'll have a look

I'll have a look at your other workbook

@ TMShucks

Your code works too...

Thank you as well...

This is soo cool. A task that normally takes god knows how many hours, can be done in split seconds.. waaaa.. I need to learn VBA.

You guys have any tips how can one learn this language?

Originally Posted by TMShucks

A slightly different approach

Regards

Hi

Try this for your second problem NB in your example, there is an additional return after the last entry in the "multiple" cells. If this is the same on all lines then the below will work, note the UBound(varBatchNo) - 1 the minus one is to remove the extra return.

You'll need to put an additional sheet in your workbook for the below:

Public Sub Splitconts()
Dim varLine     As Variant
Dim varBatchNo  As Variant
Dim varContNo   As Variant
Dim lastline1   As Long
Dim lastline2   As Long
Dim x, y        As Long

lastline1 = Sheet4.Range("A65536").End(xlUp).Row

For x = 2 To lastline1
With Sheet4
varLine = .Range("A" & x & ":Y" & x).Value
varBatchNo = Split(.Range("F" & x).Text, Chr$(10))
varContNo = Split(.Range("Y" & x).Text, Chr$(10))
End With

 For y = 0 To UBound(varBatchNo) - 1
        lastline2 = Sheet3.Range("F65536").End(xlUp).Row + 1
        With Sheet3
            .Range("A" & lastline2 & ":Y" & lastline2).Value = varLine
            .Range("F" & lastline2).Value = varBatchNo(y)
            .Range("Y" & lastline2).Value = varContNo(y)
        End With
    Next y

Next x

Set varLine = Nothing
Set varBatchNo = Nothing
Set varContNo = Nothing


End Sub

Let me know if this works for you

No probs

If possible I only want the organised data to appear on the second sheet.

Just to clarify:

Only the organised data will appear on the second sheet if you remove the desired output from below the data.

Are you saying that on your actual data there will be text below the list of names/departments?

Hi Again Kyle,

I assume the second code is for the Sheet3 of the v2 file? The code runs but unfortunately no output to Sheet4.

Also just to clarify, my data will be in the same format as in Sheet3. There are more than 300 Rows of these badly in putted data.. And there is no text below the tables that does not correspond to the data its representing, if this is what you mean.

Thanks again for the help. I really appreciate it.

Originally Posted by Kyle123

No probs

Just to clarify:

Only the organised data will appear on the second sheet if you remove the desired output from below the data.

Are you saying that on your actual data there will be text below the list of names/departments?

Try this,

I'm guessing it's to do with the sheet naming. Let me know if it works for you.

Also just to clarify, my data will be in the same format as in Sheet3. There are more than 300 Rows of these badly in putted data.. And there is no text below the tables that does not correspond to the data its representing, if this is what you mean.

Great, that means the code I posted first will work

That is perfect Kyle. You're a life saver.

So, I just paste this Code to the Visual Basic Editor of the Workbook, tweak the sheet names. Then all 300 Rows will be converted?

This is amazing.. Thank you ever so much.

Originally Posted by Kyle123

Try this,

I'm guessing it's to do with the sheet naming. Let me know if it works for you.

Great, that means the code I posted first will work

Yep, fingers crossed!!

For the first problem, you may need to tweak the columns in the code if the job title isn't in column E etc.

Superb.

Will test this now and see how it goes.

Thanks once again.

Originally Posted by Kyle123

Yep, fingers crossed!!

For the first problem, you may need to tweak the columns in the code if the job title isn't in column E etc.

Hi Kyle.

Unfortunately, it does not work. Probably to do with naming again.

In VBA Window a sheet looks like this Sheet1(Sheet1) by default. But if Sheet1 is labelled, for example Customer. It will become like this Sheet(Customer)

In your code, how did you define the sheet names?

Thanks again.

Originally Posted by crazysniper

Superb.

Will test this now and see how it goes.

Thanks once again.

Originally Posted by crazysniper

Superb.

Will test this now and see how it goes.

Thanks once again.

Generally in VBA sheets should be Sheetx(Whateveryoucalledit) if using sheetx, they don't need defining. The best thing to do is every time in my code you see sheetx, replace it with sheets("Customer") or whatever sheet you need.

Does this make sense?

OK I figured it out. Silly me. I had to reverse the name. i.e. the Sheetx from the bottom of the code should be the one on the top.So that resolves it.

Thank you ever so much Kyle.

And also thanks as well to TMShucks

Originally Posted by Kyle123

Generally in VBA sheets should be Sheetx(Whateveryoucalledit) if using sheetx, they don't need defining. The best thing to do is every time in my code you see sheetx, replace it with sheets("Customer") or whatever sheet you need.
Does this make sense?