Move cells based on cell length *shift cells right) - code required

Hi

I have a database which when I export to excel it doesnt always come out in the same format.

e.g.
AT Business 1 1
AU Business 2 2
AV Business 3 3
AW Business 4 4
AX Business 5 5
AY Business 6 6
AZ Business 7 7
BABusiness 8 8
BBBusiness 9 9

What I would like is a macro that looks at the cell lenth in column A.
If it is 2 or less then do nothing.
But, if it is more than two characters then I need it to put the first two characters in column one and then the remaining contents in column B (apart from the space) and shifting everything else along.

It would then look like this:
AT Business 1 1
AU Business 2 2
AV Business 3 3
AW Business 4 4
AX Business 5 5
AY Business 6 6
AZ Business 7 7
BA Business 8 8
BB Business 9 9

Can anyone help with the code please?

Hi,

Thanks for this code - its very close to what I require, wondered if you could help make a couple of tweaks?

Rather than it copy to a new "required" sheet can it make the amendments to the active sheet where the data is held?

The other modifaction is the business name - it currently crops the name slightly. In the attached example I have amended the business names to Business ABC - this is what I would need displayed in column B.

Okay to the first request, but need clarification on the second.
You have
BA Business 8
BB Business 9

So, how do you get to ABC?

I re-uploaded a new example as my first example might have been misleading.

If the cell is, for example, AABusinessXYZ then I would need in column A, "AA" and in column B "Business XYZ"

but when it puts "Business XYZ" in it will need to shift cells right one

I have checked both attachments, there is no update, i.e. the data are the same. It is difficult to read data on text format, so please attach an excel book which mimics your actual data.

Hi,

Your spreadsheet works perfectly - it does excatly what I need it to do.

When I paste this into my actual model though it doesnt work and I dont know why.

You will see that this report has data in rows 12-188. When it has been exported it has exported rows 12:37 correctly but not the rest.

This is why I need the macro to correct it - but on this file the macro isnt working - do I need to amend any of the code as its a new model?

It is easy to adjust the code, but do you still want words like "Page", Sub-total and Code to be included on the report as their length are >2?

Hi

The plan is to use your code after the one on this thread

HTML Code: 

So those that you mentioned should already be gone, I hope

I do not know, but I have included two codes. The top code (without and output on sheet with out) excludes those lines, while the bottom (With) includes all.
Try

Hi,

Got back to this...

I have tested the code and it deletes a column I need to keep.

Before the code is run the cells I have highlighted in yellow are present but after the code is run they are deleted. Ideally I would need to keep them and shift cells right

Can anyone help with the code for this please?

Originally Posted by pauldaddyadams

Hi,

Got back to this...



Can anyone help with the code for this please?

Who is anyone?? I was tempting to ignore the thread but since I started it, I may well finish it.
I gave you two codes. One deletes some of the lines and second one keeps all lines. It seems you have run the first code.

AB33 - I have sent a PM.

I have attached an updated workbook now I have my other macro working. When I run the test macro to format the report and run both yours I still lose a column - am I doing something wrong?

Okay! I see what is going on. Will correct it un momento

I have been working on the original data where there were only 3 columns. I have now amended code, but I am assuming you have cleared data,that is, the code looks at the cell length in column A. Since words like "Year", "Subtotal", are also over 2 length, they will be split in to 2 columns.

I can adjust the code as I did in the previous one to excluded splitting columns with sub total page and so on, but first try it the attached.

Hi - that code works. If you could include the code which will exclude "sub total" etc then I think you have cracked it for me!

Could you also include this please (Im unsure of where this would need to be included)

Please Login or Register  to view this content.

I have assumed the data first row is row 10. Try the attached.

Hi,

Just tested it - things only come to light when I see it working. What I need is that if it finds "Sub Total" or "Grand Total" it would still shift cells right. On your attached B38 I would need to be blank and then the value to shift right - so the 0.17 appears in C38. Hope that makes sense?

Try the amended.

Hi AB33 - this done it!!!! Thank you so much for all your hard work!!

Hi

I thought this was working fully but I have encountered a problem…

When the data exports from PDF correctly as in rows 12-31 on the attached the subtotal line is fine. However when its run through the macro it shifts it along right by one so its all out of sync.

Is there any way in which a new rule can be included where it says if column A is subtotal and the cell to the right of it is blank then do not shift right, if not then do what the current code is doing.

Can you help AB33 please?

Ps - thanks Shg for the spelling correction!

Yes, thats right - column B

Its Grand total also please, good spot!

Try the attached.

Hi,

This doesnt work - When I hit the macro it removes the subtotal. e.g. Row 31 on the sheet "Ori (2)" disappears.

I have updated the file to show an "Ideal tab" - this is what I am trying to get to

No, all the sheets on the attached have already been split up so when you run the code, it will not do anything. Look at column B. Either try it on original data which has not split yet, or attach an orignal sample.

This is a file showing the original data and a sheet having run the macro - the sub total lines are being deleted on the rows only where the orignally pulled correctly

Okay! Try it now

Sorry for the delay in replying - I have been testing this to make sure its correct. Sadly it isnt and I do not know why...

I have uploaded the model excatly how its exported with business descriptions as they appear on the orignal report, the only thing I have changed is the amounts.

There are two reports APP024 and APP037 that the macro will be used on.

APP024 - this is the one that isnt working as it is cutting some of the business's name. I have shown on the "APP024 Macro Run" sheet which ones have errors and what the desired results should be.

APP037 - I have only tested this brifely and it appears to all be working.

Would it be possible to take a look again please?

Hi,

I have run the other code in the attached which should get rid of all the other lines you mention - I do not understand what would be causing the text split as sometimes it works and sometimes it doesnt.

In the attached i have highlighted in yellow the bits I need to fix but I need help understanding why the code is treating these cells differently.

Ah - that does explain it. I can go in and add as many statements as possible.

Why would "Business" partially match "USE?

I dont understand why these would be blank (these appear in column A and columb B is blank)
GA
HC
HD
HE
HO

Is it only these: "Code", "Sub-Total", "Page", "Grand" its looking for? I know you mentioned "Inter" but I can not see this in your code?

I should be able to delete the business rule as this will be removed once I run my first code I think - I can not find the word business in the code, where is it?

I can then test it

I am refering to this code:

Please Login or Register  to view this content.

Module 2 on the last attachment.

I have now amended the code to pick-up exact words only, but you may hundreds of words and need to put them in the ifs statement. Please look at the attached (module 1) and see the coloured rows. row 42 is "Page 1 of 5", unless you want to include all exact match words which also means 100s of lines of code. I have used partial match to exclude all lines which have word "Page" in them, but this will also have unintended consequence matching words which have p,a,g,e in them
I am not convinced the code will work, but it is your call and need to expand the if statement for 1000s of words.

AB33 - thanks so much for sticking with me on this and explaining like you have.

I see your point. I really do not know the best way around this because as you suggest the only work around is 100 of lines of code.

I was thinking about this last night and wondered (it does mean chaning the code quite alot).

What if... say the code is only used to split the first two characters only into column A. Then, the code uses a lookup referencing a "Lookup Sheet" on that sheet it would have a list of 2 digit codes and the full name.

What do you think?

Hi,

Sorry for the dealy - I couldnt access the forum yesterday

Whats annoying is I have tried lots of PDF converters and they all seem to handle the data differently. Some reports I am using I am not even going to bother trying to reformat it as it appears on different rows, mergers data so the rules can not be written. Never knew about the unseen characters part - frustrating!

I have attached the lookup I need.

Hi pauldaddyadams,
Let's look at row 31 of lookup sheet
AZ Tflc Interface

This is found,AZ TFLC INTERFACE, on the main sheet on row 37.
Let's also assume AZ (Split part) matches AZ of column (C) of sheet lookup. If you do Vlookup, it returns the values in column D.
I need to understand what is your objective and why this is different to the previous approach?
Yes, the code will get rid of all the unnecessary lines since there are no matches on sheet look up for these rows. It only returns for values which are in column C of sheet look up.
How about if there are duplicates names?
Just food for thoughts
I do not know what software do you use to convert your PDF. As far as I know there is not perfect application. I have not used the professional adobe converter, but I have tried lots of third party software, but none of them work all the time. I know this is very frustrating. Lots of people tempted to write a VBA code, but none of them have succeeded as far as I know.

Hi

Doing it this way should correct two things:
Blank descriptions in column B
Incorrect trimming of names in column B

In the example you said for AZ TFLC Interface the code works perfectly for that already so you wouldnt see a difference.

If you take the example "BA" when the model is run the description is changed to "Not Use" rather than "Business Ba - Do Not Use" but using the lookup I am hoping it will pull the full name.

Another example is "BB" - the macro returns "INTERFACE X" rather than the full name "Tflb Interface X"

Another example is "GA" - the macro returns no description, with the lookup it should pull "Zz Eurostar (Uk) Ltd"

What do you think?

In terms of duplicates there shouldnt be any - the two letter part is unique to a customer.

I am surprised that noone has managed to create a code but I guess it must be too difficult to create so many rules as all data is different. I tried Adobe Pro but that didnt work, it was one of the worst.

The problem is not a VBA code, but the document itself. If I was so easy to use a VBA, why do you think there are many third party software on the market?
The issue you are having is still not resolved- what are going to do with all the rows where you do not want to split column A?
Let's say, forget a VBA, use Excel's text to columns function. It can split column A, but indiscriminately. It would not differentiate between a row which has other words and a row with AZ TFLC Interface, hence the reason for looping through each row (VBA) and separate which row needs to split and a row which should remain unchanged. So, Unless I am missing something, we are back to square one.

Hi, this is what I thought I would need

The code would run to either:
• Find all the instances where column A is not 2 lengths in character and replace it with two just the first two characters. It would need to exclude the sub total , grand total etc words.
• Or it could just replace column A with the first two characters (regardless if it pulled through or not correctly). Again it would need to exclude the sub total, grand total etc words.

Then the code would then say for all rows which only have two characters in length in column A, use the lookup in column B to get the customer name.

It would also have to apply the logic where if the data hasn’t pulled through correctly it will need to shift the cells to the right. Possibly, see if the data fills up to column H and if it doesn’t then select the range (for the row only) B:G and move it along one cell to the right. Or possibly if column B initially contains a number then shift to the right.

Im throwing ideas out there – feel free to pick it apart

This is my suggestion.
Start a new thread.

Do not refer it to this thread nor you should attach my code, so that others can see and start with fresh eyes and new ideas.
Please attach a sample, but the sample has to represent your actual data layout, i.e. the length of rows and columns.
We shall wait until Monday for reply. If no other alternative approach works, I will pick it up and will implement your latest ideas.

I have come up with the attached to give you an idea of a new way of thinking - just another suggestion. I will do as you suggest and see what others suggest using this as a basis