Solved

Desired Unmerge

Posted on 2013-02-04
2
238 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
[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 Comments
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) 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

Office 365 Training for IT Pros

Learn how to provision Office 365 tenants, synchronize your on-premise Active Directory, and implement Single Sign-On.

Question has a verified solution.

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

Outlook Free & Paid Tools
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
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…

737 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