[SOLVED] Extracting Data Located in Single Cell

[crazysniper]

Hi All,

I think my problem can only be done using VBA and that is why i am posting here. I hope someone can help.

Here is my challenge.

I am trying to extract individual pieces of information that exist in a singe cell, and paste them independently. Then copy one piece of information and paste them repeatedly to match the extracted data.

Please see the attached file of what i am trying to accomplish. It will probably makes more sense.

If anyone can kindly show me a quick way of doing this i would really appreciate your help. I have over 300 lines of these data i need to organise.

By the way. I am not really that good with VBA so please bare with me.

Thanks in Advance.

[TMS]

No attached file.

[crazysniper]

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

Thanks for the prompt reply.

[Kyle123]

Hi,

Try this, it will put the extracted data on a second sheet rather than below, is this what you want?

Please Login or Register  to view this content.

[crazysniper]

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.

[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

[TMS]

A slightly different approach:

Please Login or Register  to view this content.

Regards

[crazysniper]

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.

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

[crazysniper]

@ 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?

A slightly different approach

Regards

[Kyle123]

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:

Please Login or Register  to view this content.

Let me know if this works for you

[Kyle123]

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?

[TMS]

@crazysniper: you're welcome. Thanks for the rep.

I think my code would require a little more tweaking and possibly a rethink so I will leave you working with Kyle123 as his version is up and running.

In terms of learning VBA, the question has been asked before, as you might imagine. See the following thread for some ideas.

http://www.excelforum.com/the-water-...for-excel.html

The forum is a good source of inspiration and you'll find, as in this case, there can be several ways of approaching a problem.


Regards

[crazysniper]

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.

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?

[Kyle123]

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

[crazysniper]

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.

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

[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.

[crazysniper]

Superb.

Will test this now and see how it goes.

Thanks once again.

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.

[crazysniper]

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.

Superb.

Will test this now and see how it goes.

Thanks once again.

Superb.

Will test this now and see how it goes.

Thanks once again.

[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?

[TMS]

Sheets have a code name and sheet/tab name. You can change either or both. The sheet/tab name is the one that's visible.

Some examples in the Immediate window:

Please Login or Register  to view this content.

Regards

[crazysniper]

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

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?