+ Reply to Thread
Results 1 to 8 of 8

How to convert to a readable table

Hybrid View

  1. 09-17-2015, 11:54 AM #1
    Manena
    Manena is offline
    Registered User
    Join Date
    07-30-2015
    Location
    Florida
    MS-Off Ver
    2013
    Posts
    49

    How to convert to a readable table

    Hi,
    I am very happy to be part of this forum.
    All my questions has been resolved, so far, whit any problem.
    My challenge today is to try to convert a data I get from my CPA to a readable table. I am attaching the example.
    In the data sheet you can see the way she deliver to me.
    In the edit sheet you can see how I need to convert. It is a lot of editing and I am wondering if it is a way to do it whit formula.
    I am talking about over 20 thousand lines. It take me at least an hour to edit and make it readable.
    For the gurus in the forum I believed this is a peace of cake.
    Thanks an advance for your attention and help.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 09-17-2015, 12:42 PM #2
    Craig K.
    Craig K. is offline
    Valued Forum Contributor
    Join Date
    10-10-2012
    Location
    New York
    MS-Off Ver
    Excel 2007/2010
    Posts
    337

    Re: How to convert to a readable table

    If all you need is to create a column where you can filter by the account number, try the following:
    - Insert a column to the left
    - In cell A7 enter the formula =IF(ISERR(MONTH(B7)),B7,A6)
    - copy down for all rows
    Don't just use the answers provided for you. Try to understand how it works by reverse engineering or asking about it.

    Please mark the thread as [SOLVED] (Thread Tools->Mark thread as Solved) when answered.
    If you're happy with an answer given, please click the * under the person's name to boost their reputation.
    Reply With QuoteRegister To Reply
  3. 09-17-2015, 01:04 PM #3
    Manena
    Manena is offline
    Registered User
    Join Date
    07-30-2015
    Location
    Florida
    MS-Off Ver
    2013
    Posts
    49

    Re: How to convert to a readable table

    OMG, this is going to make my GL much easier.
    Thanks you so much.
    Reply With QuoteRegister To Reply
  4. 09-17-2015, 01:14 PM #4
    Craig K.
    Craig K. is offline
    Valued Forum Contributor
    Join Date
    10-10-2012
    Location
    New York
    MS-Off Ver
    Excel 2007/2010
    Posts
    337

    Re: How to convert to a readable table

    Glad I could help
    Reply With QuoteRegister To Reply
  5. 09-17-2015, 03:59 PM #5
    Manena
    Manena is offline
    Registered User
    Join Date
    07-30-2015
    Location
    Florida
    MS-Off Ver
    2013
    Posts
    49

    Re: How to convert to a readable table

    Hi Craig,
    I have another question regarding the same problem.
    If instead a month a have a name, I taught it was only to change the month for name, because I am using proper names from companies.
    I am getting the first line ok, but under are only zeros.in the blank spaces
    It looks like I can't attached anything

    This is more or less the example:


    Customer Job Act. Cost
    My House.com
    551012 100.00
    Total My House .com 100.00
    Your House
    740556 100.00
    751355 100.00
    Total Your house 200.00

    I need My House.com under 551012 and in the total as well
    The same whit the other customer Your House. I need that in the line for 740556 and 751355 and the total.
    You think you can help me? or may be somebody else?
    Thanks aging.
    Reply With QuoteRegister To Reply
  6. 09-17-2015, 04:13 PM #6
    Manena
    Manena is offline
    Registered User
    Join Date
    07-30-2015
    Location
    Florida
    MS-Off Ver
    2013
    Posts
    49

    Re: How to convert to a readable table

    I see now the attaché button.
    Please see the example.
    Thanks a lot.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 09-17-2015, 04:18 PM #7
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: How to convert to a readable table

    This seems to be able to ignore the dates and return the correct code:
    Insert a helper column A and enter the following and fill down:
    Formula: copy to clipboard 
    =IF(LEN(B7)>10,B7,A6)


    OR without a helper column added.
    Enter this in M7 and fill down. You can then select all your data and filter on column M
    Formula: copy to clipboard 
    =IF(LEN(A7)>10,A7,M6)


    K
    L
    M
    5
    Current
    6
    Amount
    7
    		 100 Chase Ach 8064
    8
    -343,510.91
    		 100 Chase Ach 8064
    9
    331,300.00
    		 100 Chase Ach 8064
    10
    3,349.15
    		 100 Chase Ach 8064
    11
    		 101 Chase 98072
    12
    -19,300.55
    		 101 Chase 98072
    13
    48,106.85
    		 101 Chase 98072
    14
    11,987.13
    		 101 Chase 98072
    15
    4,079.00
    		 101 Chase 98072
    16
    1,157.25
    		 101 Chase 98072
    17
    72,032.05
    		 101 Chase 98072
    18
    35,400.00
    		 101 Chase 98072
    19
    20,547.75
    		 101 Chase 98072
    20
    2,889.00
    		 101 Chase 98072
    21
    1,485.31
    		 101 Chase 98072
    22
    		 103 Chase Payroll 3049
    23
    -30.40
    		 103 Chase Payroll 3049
    24
    -26,259.01
    		 103 Chase Payroll 3049
    25
    -30.40
    		 103 Chase Payroll 3049
    26
    -27,253.42
    		 103 Chase Payroll 3049
    27
    56,289.41
    		 103 Chase Payroll 3049
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W
    Reply With QuoteRegister To Reply
  8. 09-17-2015, 06:10 PM #8
    Craig K.
    Craig K. is offline
    Valued Forum Contributor
    Join Date
    10-10-2012
    Location
    New York
    MS-Off Ver
    Excel 2007/2010
    Posts
    337

    Re: How to convert to a readable table

    For starters, in the new file you attached I noticed you were trying to use customer() and name() in place of Month(). Customer() and Name() are not functions recognized by Excel.

    For the sheets like the one in your second attachment place the following formula into cell A3 and copy down:
    =IF(ISBLANK(B3),A2,B3)
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formatting Ads in a Readable Manner
    By EricBarnes88 in forum Excel General
    Replies: 6
    Last Post: 07-29-2014, 08:01 AM
  2. Manually create an excel readable table
    By zoltain in forum Excel General
    Replies: 2
    Last Post: 07-28-2011, 03:44 PM
  3. Word Documents not readable
    By joe41 in forum Word Formatting & General
    Replies: 1
    Last Post: 03-28-2009, 01:28 AM
  4. Are readable, non-printing cells possible?
    By mar10 in forum Excel General
    Replies: 1
    Last Post: 02-17-2009, 02:57 PM
  5. CSV file not readable in excel
    By sepunx2 in forum Excel General
    Replies: 1
    Last Post: 01-20-2009, 06:38 AM
  6. [SOLVED] how do I convert utc to a readable time format?
    By Jon in forum Excel General
    Replies: 2
    Last Post: 06-08-2006, 09:45 AM
  7. [SOLVED] Convert Unix timestamp to Readable Date/time
    By Emily in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-26-2006, 08:00 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1