Solved

formatting excel data

Posted on 2011-02-25
19
217 Views
Last Modified: 2012-05-11
Hello,

I have an excel spreadsheet with one column of data that I would like to format with leading spaces based on the number of leading blank spaces there are currently and the data style. All of my data is in column A and I would like to format it and move it to a new worksheet, keeping it all in the same column.

So I would need some VBA code to do this:

If cell value font is in italics and has either 20 leading spaces or 35 leading spaces, indent it with 7 leading spaces.
Else if cell value font is bold and has either 15 leading spaces or 25 leading spaces, indent it with 5 leading spaces.
Else if cell value font is bold and has either 10 leading spaces or 20 leading spaces, indent it with 3 leading spaces.

I know this sounds very random, but this is how the data comes out of a Hyperion report.

If you're unsure of the answer, please let someone else take it. It seems that there's an etiquette here that if another expert is already on it, other experts won't jump in. Which is fine, but I would rather someone who really knows excel to answer the question.

Thank you.
0
Comment
Question by:erp1022
  • 9
  • 6
  • 2
  • +1
19 Comments
 
LVL 10

Expert Comment

by:cdebel
ID: 34983105
when you say that you want to indent it with 7 leading spaces, you mean that it will pass from 20 to 27 spaces, or 35 to 42 spaces?
0
 
LVL 10

Expert Comment

by:cdebel
ID: 34983108
(oh and don't worry, i won't leave you without your answer :))
0
 
LVL 10

Expert Comment

by:cdebel
ID: 34983142
If i understood what you asked for properly, it should look like that:

Public Sub FormattingData()
    For Each Cel In Sheet1.UsedRange.Rows
        If Cel.Font.Italic = True Then
            If Left(Cel.Value, 20) = Space(20) Or Left(Cel.Value, 35) = Space(35) Then
                Cel.Value = Space(7) + Cel.Value
            End If
        ElseIf Cel.Font.Bold = True Then
            If Left(Cel.Value, 15) = Space(15) Or Left(Cel.Value, 25) = Space(25) Then
                Cel.Value = Space(5) + Cel.Value
            ElseIf Left(Cel.Value, 10) = Space(10) Or Left(Cel.Value, 20) = Space(20) Then
                Cel.Value = Space(3) + Cel.Value
            End If
        End If
    Next
End Sub

Open in new window

0
 
LVL 8

Expert Comment

by:ragnarok89
ID: 34983222
Sub check()

check1a = Trim(Mid(Range("A1").Value, 1, 20))
check1b = Trim(Mid(Range("A1").Value, 1, 35))

check2a = Trim(Mid(Range("A1").Value, 1, 15))
check2b = Trim(Mid(Range("A1").Value, 1, 25))

check3a = Trim(Mid(Range("A1").Value, 1, 10))
check3b = Trim(Mid(Range("A1").Value, 1, 20))

    If (Range("A1").Font.Italic = True) And (check1a = "" Or check1b = "") Then
       Range("A1").Value = "       " & Range("A1").Value
    End If
   
    If (Range("A1").Font.Bold = True) And (check2a = "" Or check2b = "") Then
       Range("A1").Value = "     " & Range("A1").Value
    End If
   
    If (Range("A1").Font.Bold = True) And (check3a = "" Or check3b = "") Then
       Range("A1").Value = "   " & Range("A1").Value
    End If
   
End Sub

0
 
LVL 10

Expert Comment

by:cdebel
ID: 34983229
This code will add 7, 5 or 3 additionnal spaces at the beginning of your cells depending on their values & font style.

If what you meant is to keep only 7, 5, or 3 spaces at the beginning of the cells, then maybe you could type instead of having:

     Cel.Value = Space(7) + Cel.Value

use:

     Cel.Value = Space(7) + Ltrim(Cel.Value)

It will trim all the spaces at the beginning of the cells, and leave only 7 spaces...

0
 
LVL 10

Accepted Solution

by:
cdebel earned 500 total points
ID: 34983272
So basically, the code would look like this:

Public Sub FormattingData()
    For Each Cel In Sheet1.UsedRange.Rows
        If Cel.Font.Italic = True Then
            If Left(Cel.Value, 20) = Space(20) Or Left(Cel.Value, 35) = Space(35) Then
                Cel.Value = Space(7) + LTrim(Cel.Value)
            End If
        ElseIf Cel.Font.Bold = True Then
            If Left(Cel.Value, 15) = Space(15) Or Left(Cel.Value, 25) = Space(25) Then
                Cel.Value = Space(5) + LTrim(Cel.Value)
            ElseIf Left(Cel.Value, 10) = Space(10) Or Left(Cel.Value, 20) = Space(20) Then
                Cel.Value = Space(3) + LTrim(Cel.Value)
            End If
        End If
    Next
End Sub

Open in new window

0
 
LVL 8

Expert Comment

by:ragnarok89
ID: 34983294
Cdebel,

   I never realized you could do it that way... very nice.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34983575
Ahh, an opportunity for E-E exchange and learning, so here goes...

I've never read a book, manual, or web page on E-E etiquette in the context you describe.  My experience is E-E experts contribute on everything they believe they can contribute to, some one-upping the others in a fair comradare to ensure the best solution is provided, and learning experience, exchange occurs.  There is nothing gained by responding to a question for the expert, other than the opportunity to contribute, to learn, and to get points which mean almost nothing beyond the ego boost gleaned from helping someone else.

You can view each E-E expert's qualifications before reading the response if you choose, but then all E-E experts started out with 0 points and 0 certifications - I was there 2 months ago, because I never contributed before then, though I've been a member since 2004.

The solution to this question is fairly trivial and I believe most E-E experts can handle.  In fact, with a bit of thought and guidance, you can do this yourself!

Just write a simple vba routine looping through the used range of column A
  within that loop
     check the font style and leading spaces of each cell that has data
        based on font style and leading spaces, change the value of the cell by trimming it, then padding with # spaces based on your "rules"

That's it!

If you're unsure how to write this code, just ask!

I'm sure by this time, some very good E-E experts have already responded - so, enjoy!

Dave
0
 

Author Comment

by:erp1022
ID: 34997602
Dave, not knowing VBA, no I cannot do this myself. That's why I (and many others) sign up for and pay a monthly fee for Experts Exchange, so that we can get some assistance. And that's what I'm doing, 'just ask'-ing. As for the EE etiquette: I have never read a book, manual, etc etc on SQL and ETL and have learned on my own through googling and posting questions on various forums and message boards. IN MY EXPERIENCE on EE, I have had some alleged Experts attempt to answer questions, then when not being able to figure out the answer, just drop off. Again, IN MY EXPERIENCE, it seems that in some cases other experts won't answer a question once it's been answered by someone else, so the question never gets answered and I have to repost. IN MY EXPERIENCE, my question is not 'trivial' as you so condescendingly put it. If you have had other experiences here, good for you! I'm just stating what I've encountered, which is not for you to judge or comment on. If you have nothing constructive to contribute to helping me with the VBA code, could you kindly stay off of this question?

To the other experts: thanks so much. I will test out the proposed solutions shortly.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 10

Expert Comment

by:cdebel
ID: 34997834
it seems that you both got an issue together.  I don't know what happened between you but as many people on E-E would say, we have to focus on the question.  I didn't commented when dlmiles posted that "solution" because i knew there was a problem.

I start to get used to dlmiles method (see a post that i answered here: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26851340.html?cid=1131#a34993072).  Yes, it's annoying.

I cannot speak for others, but i prefer to not answer when someone started to give an answer (unless this person is a real d... a.. who lead him to the dump).  It's less comfusing for the askers when he get answered by one person solution.   And when i see that the expert is rorya, brettdj, mathewspatrick, i know the asker is in good hand.
0
 

Author Comment

by:erp1022
ID: 34998268
Yes, I definitely have an issue with people posting irrelevance on a question when I am trying to get something work-related accomplished.  

cdebel, I tested your solution ID #34983272. When I run the macro nothing happens. Please see the attached file, this is sample data, but this is how the data comes out of Hyperion. As you can see, it's 'tiered' at different levels depending on whether it's a parent account, child account or a line description. And then it's also tiered at different levels depending on whether it's a Revenue or an Expense.

So I want all of the account starting with 'S' (these are the child accounts) to have a leading space of 7. All of the accounts that start with 'X' and 'V' to have a leading space of 5, and all of the Line Description to have a leading space of 3.
Sample-2.xls
0
 
LVL 10

Expert Comment

by:cdebel
ID: 34998365
I don't understand how you tried to put my code in your sheet, but it work for sure.

See the file YourSampleWithVBA.  Run the macro in it, and you will get the result in Result.xls.

As you can see, it's all indented properly...
Items starting with S have only 7 spaces at the beginning of the cell,
Items starting with X or V have only 5 spaces at the beginning of the cell, and
Items starting with Line Description have only 3 spaces at the beginning of the cell.
YourSampleWIthVBA.xls
Result.xls
0
 
LVL 10

Expert Comment

by:cdebel
ID: 34998390
Make sure that the sheet where your data reside is named Sheet1.  Because as you can see on line 2, it's the name of the sheet that we are working on in this sample.  If you want to keep a different name, make sure to change the sheet name in the "For Each"
0
 

Author Comment

by:erp1022
ID: 34999336
When I run the macro in my data file, I get the following error message...


Macro-error.bmp
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34999444
I should probably ignore and move on.  And, I probably should have ignored what I perceived as an insinuation from the OP.  However

Gentlemen - my answer was not irrelevant, as I was responding to what I perceived as an insinuation (which I discussed with other EE'ers whose Id's were in cdebel's "distinguished" list, and they didn't feel my post on this question was out of turn).  My post on cdebel's other question was a mistake and I apologized for that - cdebel - this is "not" my method.  I don't make a habit of doing that and I thought I was correcting an error and, to my chagrin, I was in error (acknowledged in the post).

@erp - thanks for your response, and I appreciate your comments in the original post much more clearly.

There's no need to respond, as I'm just clarifying the record.  If you want to see how I handle 99.9% of questions, feel free to review my questions answered - that's the real record.

Peace,

Dave
0
 

Author Comment

by:erp1022
ID: 34999508
Ugh, can people please stop posting stuff on here unless it's for the VBA code and getting this resolved?? It's irrelevant and totally unnecessary.

cdebel - I had put my code on the tab 'this worksheet', ran it, got the error, and then took a look at your workbook. I saw that you put yours on Sheet1, so I changed mine. I am not getting the error, but I run the macro and nothing happens. Nothing at all, no error, but it's still not formatting the data.
0
 

Author Comment

by:erp1022
ID: 34999798
Okay, I'm fairly certain this is working now. I just have to set up another macro to copy and paste as values so that your macro will work.

I'm going to play around with this some more, but I'm pretty sure you've solved the problem. Thank you so much!!
0
 
LVL 10

Expert Comment

by:cdebel
ID: 34999960
alright, glad i helped.  If you still have difficulties about this, just buzz me.
0
 

Author Comment

by:erp1022
ID: 35016971
This works beautifully, thanks again!
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now