• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Aligning data automatically in an excel sheet

dvm49

Member
I have a raw data (please refer attached file: raw_data.txt). So when I copy the attached raw data to the excel sheet, I want it to be aligned automatically like this (needed_format_on_pasting_raw_data.xlsx). Is that possible? Any help would be appreciated.
 

Attachments

  • raw_data.txt
    870 bytes · Views: 5
  • needed_format_on_pasting_raw_data.xlsx
    9.4 KB · Views: 0
Why not set the alignment via the ribbon, try not to select a whole column as Excel will reserve memory for the whole column even if you only use a few rows.

7upload_2017-11-21_19-5-8.png
 
Hi ,

What you want done is not possible , unless you use VBA to convert the data in the text file or after it has been imported into Excel , to the desired format.

Narayan
 
@bobhc, @NARAYANK991 thank you for your comments. Is it possible for you to help me with VBA? The reason that we need this is for other data manipulations. In the initially attached file (needed_format_on_pasting_raw_data.xlsx), there is an error.

I have attached both the files again in this message. Please have a look. If you look into the attached raw_data.txt, under "All Cancelled Trips" it does not have LOC3 and LOC4. So while pasting the data, I need to insert those and make the count to show as "0". Similarly for other sections too.

Basically, LOC1,LOC2,LOC3 & LOC4 are fixed locations. If in the raw_data, if any location is not listed. We need to just add the LOCID and assume the values to be zero.

Any help on this would be appreciated.
 

Attachments

  • raw_data.txt
    870 bytes · Views: 6
  • needed_format_on_pasting_raw_data.xlsx
    9.5 KB · Views: 0
What i suggest!
Copy-paste or import data in excel & then use formula in layout to grab the values!!

=IFERROR(VLOOKUP($A3,OFFSET($F$1,LOOKUP(,-1/($F$2:F3="rid"),ROW($D$2:$D3))-1,,5,4),COLUMN(),0),0)
 

Attachments

  • needed_format_on_pasting_raw_data.xlsx
    10.7 KB · Views: 5
Thank you @Deepak, I was looking for a VBA way to do this stuff. As there is some manual work involved in using the formula. The main intention here is to avoid errors. So it would be nice, if this entire thing could be automated via VBA.

Also I see that you have applied the formula to the already aligned area. When you copy the data, the alignment won't look like that.
 
Just to simplify and make things clear for everyone. I wanted to have two macros one for inserting the missing "locations and headers". And the other for sorting the location ids from larger to lower.

I have attached a file with this message for better understanding. Sheet1 has the raw_data pasted to it where some areas are missing LOCID and header.

Requirement1:
Sheet2
, I have manually inserted the LOCID (highlighted in red) in the missing areas and header (highlighted in green) in the missing areas. So this task has to be done via macros.

Requirement 2:
After inserting the Location ID into the missing areas. I need to sort the location ID's from higher to lower via a macro, I have manually done in Sheet3 for reference.
 

Attachments

  • needed_format_on_pasting_raw_data.xlsx
    13.7 KB · Views: 0
Hi ,

Sorry for the delay.

See the attached file.

I have used your first uploaded file as a template.

Every time you run the macro named ConvertData , it will copy the worksheet tab to a new worksheet which will then be renamed Converted Data.

Using this worksheet as the template , it will copy all the raw data from a worksheet which it expects will be named Input Data.

There is very little error checking , and in case your input data format changes , it will require changes to the code.

Narayan
 

Attachments

  • ConvertData.xlsm
    25.5 KB · Views: 3
Back
Top