Solved

Quick ways to clean up data downloaded into Excel from accounting programme?

Posted on 2014-09-28
6
121 Views
Last Modified: 2014-10-16
hi Folks
Often when data is downloaded from Quickbooks/Sage into Excel it's usually got lots of merged cells, extra columns, extra rows etc...any suggestions on how to quickly clean them out to get the data in clean Excel list format i.e. no blank rows, no blank columns. Thanks
0
Comment
Question by:agwalsh
  • 2
  • 2
  • 2
6 Comments
 
LVL 68

Assisted Solution

by:Qlemo
Qlemo earned 250 total points
Comment Utility
The most obvious way is to record a macro doing the work:
go to cell A1
shift ctrl csrdown
shift ctrl csrright
cell format: ...
0
 
LVL 90

Accepted Solution

by:
John Hurst earned 250 total points
Comment Utility
When I use QuickBooks, I try to make sure the report does not include superfluous data and then that limits the extra rows and columns. It generally is not a problem.

Try the Transaction Summary Report which is more geared to a data dump.
0
 

Author Comment

by:agwalsh
Comment Utility
@Qlemo - thanks for that...any idea where I'd get the code to do that...and I'll pass it on.
@John Hurst - will suggest that to the person...
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
"Record a macro" - start the macro recorder, and perform the necessary actions. This creates VBA code (with a lot of overhead, but working).
0
 

Author Closing Comment

by:agwalsh
Comment Utility
Both good suggestions but John Hurst's answer a better fit for my specific question.
0
 
LVL 90

Expert Comment

by:John Hurst
Comment Utility
@agwalsh  - Thank you for the update and I was happy to help;
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

743 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

18 Experts available now in Live!

Get 1:1 Help Now