Solved

Desired Unmerge

Posted on 2013-02-04
2
200 Views
Last Modified: 2013-02-04
Hello All,
Column A has a collection of team names. Each team has its list of items it uses.
Report comes in such format that: the cells in column A are merged as one cell for Each team.
Requirement:
1.      First un-merge the column A
2.      team names repeat for each cell underneath them, till they find another team name string
FYI: Dealing with 40000 rows of this  data type. So fastest way is much preferred

thanks
desiredFormat.xlsx
0
Comment
Question by:Rayne
2 Comments
 
LVL 50

Accepted Solution

by:
teylyn earned 500 total points
ID: 38852498
Hello,

VBA-free approach: Select the cells in column A and unmerge.
Select column A, hit F5 > Special > tick "Blanks" > hit OK
Now all blank cells in column A are selected.
Without changing the selection, type a = sign, hit the up arrow key
This will produce a formula like =A3
Hold down the Ctrl key and hit Enter.
This will enter the formula into all the selected cells at once.

You can now copy column A and paste its values only into column A again to replace the formula with the values.

Total time: less than 10 seconds.

cheers, teylyn
0
 

Author Comment

by:Rayne
ID: 38852531
Thank you Teylyn, You ROCK !!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

759 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

20 Experts available now in Live!

Get 1:1 Help Now