Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts

    Look for last data column and fix column like final sheet

    Hi all,


    i have a scenario i have to convert pdf to excel when i convert some times the data extract to excel columns a to l some times a to m and so on . I want that code look to last used column of excel file and fix last column of samples sheet to column k like final sheet, second last column to final sheet column j 3rd last column to final sheet i column as like sheet (final) and so on till column a

    i have attached a samples file which contains different samples and the final sheet also.


    Thanks and regards,
    farrukh
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,813
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi farrukh

    I suspected you would have trouble with the data extracts!
    The extracts don't always follow exactly the same layout.
    If it is a case of 'empty' columns appearing in the extracts, then the simplest way is to detect the empty column and then delete it.
    To detect an empty column, you could put a simple formula in (unused) row 1:
    So, in cell [A1] on sheet [Sample1] put the formula
    =COUNTA(A2:A102)>0
    ..and copy this formula cell across to [A1:P1] etc.
    Columns which have data will show a result of True; delete any column that shows a result of False.
    This formula assumes there are up to ~100 rows of extract data.
    Change it to
    =COUNTA(A2:A502)>0
    ..to check extracts of up to ~500 rows etc.

    Hope this helps

    zeddy

  3. #3
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Hi Zeddy ,

    The problem i have facing that i need the fix sheet when i extract then run a code on excel that fix columns data like final sheet, because this sheet i have to tranfer to database and my loader depends on cell based if value is placed at column k then the loader works other wise will load wrong values. i have many converted excel file it is very difficult to handle with formula ?

    Thanks
    farrukh

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,813
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi farrukh

    You need to use a combination of both: formulas to do the alignment checks and VBA to automate it.
    You can use VBA to put the formulas in and then cut out 'empty' columns based on the formula results.
    As unwanted columns are 'removed', this will cause the other columns to 'shift left', so you will end up with your required [K] column.

    zeddy

Posting Permissions

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