Cleaning up consolidated data in a list

Please, can anyone tell me a better way to clean up a spreadsheet that looks like this?  The data comes in 3 columns, but the user puts several vendors in one cell instead of creating a new row for each vendor.

Part    Vend         Price
123     abc, def    100.00 
234     abc           75.00
345     def, ghi     70.00

I need it to look like this:
Part    Vend    Price
123     abc      100.00
123     def      100.00
234     abc      75.00
345     def       70.00
345     ghi      70.00

The problem is I'm getting data from outside sources who don't always put it in the right format.  I spend a lot of time cleaning it up.  Currently, I do a text to columns on the vendor, then paste the appropriate rows to the end and delete any excess data.  I tend to get duplication because there are so many steps (especially when there are several vendors in a single cell.) that I don't always clean up before pasting again (not sure if that makes sense.)  Anyway, if anyone has a better idea let me know.  Thanks!
0
anonymous (74722)
5/21/2004 5:51:02 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
358 Views

Similar Articles

[PageSpeed] 13

Hi, Kelly,

You might play with this macro - it assumes that
a comma separates your vendors and that there are
only two vendors in col B. (you can run it twice 
if there are three, etc).  NOt the most efficient
code, but it may do you OK.

Sub spreadData()
Dim r As Range
Dim lastrow As Long
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set r = Range("A" & lastrow)
For j = lastrow To 2 Step -1
Range("A" & j).Select
xx = InStr(1, ActiveCell.Offset(0, 1), ",")
If xx Then
    ActiveCell.EntireRow.Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Select
    Selection.Insert shift:=xlDown
    ActiveCell.Offset(-1, 1).Value = Trim$(Left
(ActiveCell.Offset(0, 1), xx - 1))
    ActiveCell.Offset(0, 1).Value = Trim$(Right
(ActiveCell.Offset(0, 1), xx))
End If
Next j
End Sub

jeff
>-----Original Message-----
>Please, can anyone tell me a better way to clean up a 
spreadsheet that looks like this?  The data comes in 3 
columns, but the user puts several vendors in one cell 
instead of creating a new row for each vendor.
>
>Part    Vend         Price
>123     abc, def    100.00 
>234     abc           75.00
>345     def, ghi     70.00
>
>I need it to look like this:
>Part    Vend    Price
>123     abc      100.00
>123     def      100.00
>234     abc      75.00
>345     def       70.00
>345     ghi      70.00
>
>The problem is I'm getting data from outside sources who 
don't always put it in the right format.  I spend a lot 
of time cleaning it up.  Currently, I do a text to 
columns on the vendor, then paste the appropriate rows to 
the end and delete any excess data.  I tend to get 
duplication because there are so many steps (especially 
when there are several vendors in a single cell.) that I 
don't always clean up before pasting again (not sure if 
that makes sense.)  Anyway, if anyone has a better idea 
let me know.  Thanks!
>.
>
0
anonymous (74722)
5/21/2004 6:51:31 PM
Kelly,

Assuming your vendor codes are in the second column of your table, select a
single cell in your table, and run the macro below. Also assumed is that the
vendor list is always separated by a comma and a space.

HTH,
Bernie
MS Excel MVP

Sub KellyCleanUp()
Dim myCell As Range
Dim comCount As Integer

For Each myCell In ActiveCell.CurrentRegion.Columns(2).Cells
    comCount = Len(myCell.Value) - Len(Replace(myCell.Value, ",", ""))
    If comCount > 0 Then
        myCell.EntireRow.Copy
        myCell(2).Resize(comCount).EntireRow.Insert
        myCell.Resize(comCount + 1, 1).Value = _
            Application.Transpose(Split(myCell.Value, ", "))
    End If
Next myCell

Application.CutCopyMode = False

End Sub


"Kelly" <anonymous@discussions.microsoft.com> wrote in message
news:677B50B2-81F6-4A82-BFD8-CC2CE7440341@microsoft.com...
> Please, can anyone tell me a better way to clean up a spreadsheet that
looks like this?  The data comes in 3 columns, but the user puts several
vendors in one cell instead of creating a new row for each vendor.
>
> Part    Vend         Price
> 123     abc, def    100.00
> 234     abc           75.00
> 345     def, ghi     70.00
>
> I need it to look like this:
> Part    Vend    Price
> 123     abc      100.00
> 123     def      100.00
> 234     abc      75.00
> 345     def       70.00
> 345     ghi      70.00
>
> The problem is I'm getting data from outside sources who don't always put
it in the right format.  I spend a lot of time cleaning it up.  Currently, I
do a text to columns on the vendor, then paste the appropriate rows to the
end and delete any excess data.  I tend to get duplication because there are
so many steps (especially when there are several vendors in a single cell.)
that I don't always clean up before pasting again (not sure if that makes
sense.)  Anyway, if anyone has a better idea let me know.  Thanks!


0
Bernie
5/21/2004 7:00:17 PM
I'm so-so with macros.  But is there a way to access a macro in many different documents.  Not just the one you create it in?
0
anonymous (74722)
5/21/2004 9:01:06 PM
Hi
store the macro in your personal.xls file. See:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
Regards
Frank Kabel
Frankfurt, Germany

"Kelly" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag
news:BC8BF70B-26C4-4A16-9841-352D5B78E37B@microsoft.com...
> I'm so-so with macros.  But is there a way to access a macro in many
different documents.  Not just the one you create it in?

0
frank.kabel (11126)
5/21/2004 9:16:45 PM
Thank you all for your help.  What a great time-saver!
0
anonymous (74722)
5/21/2004 9:56:03 PM
Reply:

Similar Artilces:

Share data across web applications?
I would like to share data across two separate web applications. Is this possible, other than with Cookies? If so, how might I do it? I would like to pass non-sensitive information from one web application to another, on the same web server and running within the same website. I'm using .NET Framework v2.0 on a Windows 2003 server (IIS 6.0). Jason Barnett wrote: > I would like to share data across two separate web applications. Is this > possible, other than with Cookies? If so, how might I do it? > > I would like to pass non-sensitive information from one...

Importing five row list from word 2003 to excel
I have a Word five row list I would like to import into Excel in a five column list. Every hundred items or so their is a new heading. What do I do? If it is in a word table you can cut and paste it. But you will have to reformat it. Once you get it into excel you can sort it and delete the duplicate headers. --- Message posted from http://www.ExcelForum.com/ ...

Contacts are not listed in address book
When I open my address book, there are only two contacts. When I open my contact list it lists all my contacts. What do I need to do to get my full contact list into my address book? Depends a bit on what you have done so far and which version you have. You have given us very little information. Try this: right click your Contacts folder | Address Book | set as an Outlook addess book (tick the box). If it doesn't work, tell us version and how you got your Contacts into Outlook (import etc). -- Regards Judy Gleeson MVP Outlook "Docmoney" <Do...

Text to Columns from drop down list update
Hi I need to perform a Text to Column conversion from a drop down list, but I also need the extracted value to be updated if the value in the list is changed. eg: Drop down list has 2 values: 1. 4x16K 2. 8x8K If the user selects 1 I can easily extract out to another cell the value 1 using Text to columns, however if the user then changes the choice to 2 the text to columns extraction is not updated to 2. Is there a way to update changes in the original cell using text to columns? Or is there another way I can assign a value to a drop down menu choice in a different cell while havi...

List of files
All I want to do is get a list of files in a directory so they can be deleted. How do I get this list ? John, > All I want to do is get a list of files in a directory so > they can be deleted. How do I get this list ? You can use FindFirstFile/ FindNextFile/FindClose API functions for enumerating files. If you want to remove directory with its content take a look at SHFileOperation in MSDN. -- Regards, Kobi Ben Tzvi "John" <john.f.brownell@wdc.com> wrote in message news:022b01c3788a$bb119ee0$a301280a@phx.gbl... > All I want to do is get a list of files in...

Keeping the format but update the data
I have a report I run everyday. I want to update the data, but keep the column sizes and formatting. Any suggestions? Anybody suggest a good ebook to become more formuliar with excel? JoeM JoeM, this could be as simple as recording a macro during the formatting of the sheet after importing the data once. Then each day, after importing the new data, you simply run that macro. As for online eBook's for Excel, I'm not actually aware of any although I suspect a Google search would turn up several. It's hard to beat the "For Dummies..." series of hardcopy...

Favorite Folders reverts to default list when computer shut-down
I have no problem adding, deleting or moving folders in the Favorite Folders pane. The problem is that they all disappear and revert to the default list everytime my computer is shut down. So I have to keep adding the folders back in Is there a way to keep the shortcut folders in the Favorite Folders pane? ...

Validation
I would like to have combo box functionality for the data validation feature in Excel 2000. This doesn't seem to be available in the native validation setup dialog box. Am I missing something? I would like to display the validated items list in the leftmost column and have column(s) of description display to the right of each list item (so I can tell which list item I should select). Suggestions? Many Thanks, Bill Vallance Bill Debra Dalgleish has instructions for creating dependent DV lists. http://www.contextures.on.ca/xlDataVal02.html Gord Dibben Excel MVP On Tue, 08 Jun 20...

How do I print a list of my E-mail folders?
I need to print a list of my e-mail folders. "AHD23" <AHD23@discussions.microsoft.com> wrote in message news:5BFD12CA-5D55-4376-AF9C-58703042577D@microsoft.com... >I need to print a list of my e-mail folders. I don't believe there's a way to do that, except as a screen shot. -- Brian Tillman [MVP-Outlook] This might be a workaround for you: http://www.vboffice.net/sample.html?mnu=2&smp=12&cmd=showitem&lang=en In example #1, replace the line LoopItems Folder.Items by Debug.Print Folder.Name After running the code,...

Constant List
Is there an easy way to return the values of the ChartType constants? Excel 97 does not seem to list them in help and I have not been able to find them in google.group searches. Thanks M Select xlChartTypes in the Classes pane of the object browser. The constants are named in the members panel. If you select one, it provides the numeric equivalent at the bottom of the object browser, e.g., Const xlArea = 1 Member of Excel.XlChartType - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ M wrote: > Is there...

How do I get the data/xml/export option
My export option is disabled under data/xml tab. What is the exact feature I need to install to get this export xml option. I didn't install all the option during excel installation. Which specific install tab are required during excel installation? thanks Nick One way would be to do a web query, first preformat the cells as text, then do the query (data>import external data>new web query), put in the web site address, select the part you want to import, under properties select preserve cell formatting and do the import -- Regards, Peo Sjoblom (No private emails pleas...

I would like to export the data from a drop-down list to a table
I have a form with several drop-down lists, I need to have the information in these lists in another document/spread sheet. Is there an easy way to copy the data in these lists to another location? The long way would be to re-type all of it. ...

'Recently used file' list.
When I open Excel nine filenames appear on the RHS as 'recently used files' (this number was set via Tools/Options/General, of course). In the middle of this list are 3 no-longer-used files. Though the files themselves have been deleted their names remain in this list. Is there an easy way to remove those entries from that list, please? TIA, DB. There is an entry in the help file "Display or hide recently used workbooks on the File menu" which says uncheck the 'recently used files' checkbox, the click OK. Then, basically, start again. "DB.&...

Import data from Excel with main tasks and subtasks
I'm new to MS Project and need help with importing data from Excel into Project 2000. The data consists of Work Request numbers, individual phase numbers for the Work Request, name of each phase, and (phase) start and end dates. I can open the Excel file in Project and map the fields, however, the data shows up as individual tasks. I need to have the Work Request number and project name be a main, or top-level, task, and the data related to the phases be sub-tasks. Here's an example of how the raw data appears in the Excel file: WorkReq# ProjectName Phase# P...

Can I move the data files?
I just installed Money 2005. I let it take most of the defaults, It choose to store the data files in \My Documents. I would like to move all of them to \My Documents\My Money. Is there a way to do that? -- Simplest way is to use My Computer or Windows Explorer. Navigate to your ..mny file, click on it, click on edit > copy, navigate to where you want the ..mny file to live and click on edit > paste. When the file is in it's new home, double click on the file name. This will call up Money and this will then remember where it found the file. Now go back to the original locati...

Move data from column to rows HELP!!!
Hi thanks for taking the time to look at my problem, currently i have column that has thousands of rows of information in it, it looks lik this A 40432 432654 3432 532543 32432 523 53425 532532 532 523 532 111 222 333 666 numbers that go on into mabye the 5000-6000 range what i need to do is have that data moved So it looks like this A | B | C 40432 | 32432 | 532 432654 | 523 | 523 3432 | 52432 | 111 532543 | 532532 | 222 So on and so on, so instead of 1 column with 6000 lines it ...

Copying specific data from Word to Excel
What I need to do is 1) Extract the monetary amount (always after the 2nd asterisk (*) after the letters BPR. 2) Extract the number (always after the 2nd asterisk (*) after the letters TRN. 3) Place the numbers in the same row on an Excel spreadsheet. 4) Repeat to the end of the document. Sample data BPR*I*925.79*C*ACH*CCP*01*xxxxxxxxxxxx*DA*xxxxxxxxxxxxxxx*xxxxxxxxxxxxxxx**01*xxxxxxxxxxxx*DA*xxxxxxxxxxxxxxx*20091126~TRN*1*999999999*xxxxxxxxxxxx The file I need to extract from is always text. Is this better done from a Word macro or an Excel macro. Or can it be done at al...

CC automatically from distribution list
I am trying to set up a distribution list and trying to automatically cc people from this list (but now all). Is there someway to do this or a macro that can allow this to happen. ...

Free/Busy data setting in OWA
We implemented Exchange 2003 and pushed a client setting to all users to extend the number of months available in free/busy to 12 months with a group policy. It works fine if everyone is using the outlook client when they are in the office. But, if a user works at home one day or goes to a conference and does anything that updates the free/busy data, the OWA overwrites the users free/busy data setting it back to 2 months of free/busy available. Where can I set the free/busy data number of months available, in OWA? Is it a policy setting? On Wed, 31 Jan 2007 13:54:01 -0800, SKS <sks...

Import data not in correct format
Import data reads: Need to do statistical anaysis on hundreds of numbers and Cell value # of Cells don't want to enter the numbers by hand. 3 1 4 2 Would like: Cell Value 3 4 4 ...

Address list filter rules
I am trying to create a new address list in Exchange 2003. What I want is all employees and all employee distribution groups. After trying several combinations I added a custom attribute to each employee and to each employee distruction group and entered "Employee" as the value. I then created a new address list with the filter rule which said if the custom attribute 1 = Employee include in the list. Everything is fine except that Query based distribution groups do not show up. So I create a new address list and found that using any filter rule rejects all query based distributi...

Restoring Exchange Data
HI. I experienced a crash with my Windows 2000 server which is the Global Catalog and dns server. Anyhow in my infinite wisdom I decided to do soemthing with the exchange server and in the meantime - it blew any active directory info which had replicated to the exchange server. The Exchange server was a part of the domain with AD installed, etc. The exch. server is a running windows 2003 server w/ exch. server 2003. Fast forward...I rebuilt the Windows 2000 server and its backup. I also rebuilt the Exchange Server...not completely knowing how to proceed....I installed w2k3 with all ...

Adding data to a list
I have a list of data that I want to add to or subtract from and I would like to be able to click on a button which would bring up a text box where I could insert the new data then sort the new list. Thanks, Mike Take a look at data>form -- Regards, Peo Sjoblom "Mike" <mike@ehb-docks.fsbusiness.co.uk> wrote in message news:bultp0$8ee$1@news8.svr.pol.co.uk... > I have a list of data that I want to add to or subtract from and I would > like to be able to click on a button which would bring up a text box where I > could insert the new data then sort the ne...

center data labels on my column chart
How do i get my data labels to appear in the center of each column on my chart? Thanks. Click on one of the labels (that will select all the labels in the series) Choose Format>Selected Data Labels Select the Alignment tab From the Label Position dropdown, choose Center Immediately after formatting the first series, if there are other series in the chart -- Select a label in another series Press the F4 button on the keyboard, to repeat the formatting from the previous series Repeat for all series Dan wrote: > How do i get my data labels to appear in the center of > e...

how do I find a list of databases with correpsponding company name
Hello, I need to know how in GP to pull up a list of our companies with their corresponding SQL database names. I've done this a long time ago and remember it being easy but cannot remember where to do this. SELECT INTERID, CMPNYNAM FROM DYNAMICS..SY01500 "Tim Quiggle" <Tim Quiggle@discussions.microsoft.com> wrote in message news:D0ECF5C5-74F9-494E-8C2E-B37EE12C8D0C@microsoft.com... > Hello, > > I need to know how in GP to pull up a list of our companies with their > corresponding SQL database names. I've done this a long time ago and > rem...