?
Solved

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

Posted on 2014-09-28
6
Medium Priority
?
129 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 70

Assisted Solution

by:Qlemo
Qlemo earned 1000 total points
ID: 40348820
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 97

Accepted Solution

by:
Experienced Member earned 1000 total points
ID: 40349128
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
ID: 40349449
@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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 70

Expert Comment

by:Qlemo
ID: 40349451
"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
ID: 40383804
Both good suggestions but John Hurst's answer a better fit for my specific question.
0
 
LVL 97

Expert Comment

by:Experienced Member
ID: 40384001
@agwalsh  - Thank you for the update and I was happy to help;
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

741 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