Merging data from similar fields

[adamf]

Hi,

I have a spreadsheet that lists a number of locations that containers have been imported to or exported from. The problem is that the people who supplied the data have enterend numerous rows for similar locations, for example:

Yeovil 1 3 4
Yeovil, Somerset 0 2 2
Yeovil - Somerset 7 1 8

I am looking for a way to get all the Yeovil data into 1 row, so it looks like this:

Yeovil 8 6 14

The spreadhseet is >4000 rows long, so with MANY duplicates, so I'm after a simple way to tweak this data.

Cheers,

Adam

Adam F
Solaris System Administrator

 

[Welshbird]

I would be tempted to begin by using the text-to-columns, and then seeing if this at least means you can get all your Yeovils together. I appreciate this won't help if they have entered
Somerset - Yeovil
though.

It might make it easier to deal with though.


Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[adamf]

I've already done a sort, so I can see all the "Yeovil" entries together. The data needs to go to the Board in as compact a format as possible, so I still need to clear it up further.....

Adam F
Solaris System Administrator

 

[SkipVought]

Hi,

Use MS Query, via Data/Get External Data/New Database Query -- EXCEL files -- YOUR WORKBOOK...

Row 1 must be column headings.

First get a unique list of what's in column A

You will have to edit the list in order to generate the unique list that you need. This list can ALSO be used as the souce for Data Validation to LIMIT future entries to only include what is in the list.

If you can get this far, then the next step will involve either some spreadsheet concatenation or VBA code to get that list into a dfferent format for the query. Post back when you are ready.

Skip,
[sub]
[red][/red]
[/sub]

 

[AnotherHiggins]

You can use SumProduct to pull together all values containing "Yeovil".

The problem is that you cannot use a wildcard in SumProduct. But you can use Search (or Find, if you want case-sensitive results) within a SumProduct.

So, Adam, your first challenge will be to get a list of unique locations. Once that is accomplished, you can use a formula like the following one to consolidate variations of that location. Let's say you have the list of unique locations in column G. G1 is Yeovil. In, say, H1, you can place this formula:

[COLOR=blue white]=SUMPRODUCT(ISNUMBER((SEARCH($G1,$A$1:$A$10)))*(B$1:B$10))[/color]

Then fill that formula two cells to the right and as far down as needed.

NOTE: You'll have to keep a careful eye out for entries that are misspelled. If someone submits Yeov[red]e[/red]l instead of Yeovil, for example, that will throw everything off.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Welshbird]

Well if you did use text-to-columns, you could get all of the Yeovil thngs as just Yeovil.

Couldn't you?

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[adamf]

Text to columns would indeed just give me "Yeovil". How would I then concatenate the data so that I only had one line entry for yeovil with the sum of the data for each entry?

Adam F
Solaris System Administrator

 

[AnotherHiggins]

You definitely do not want to have

Yeovil 1 3 4

all in one cell, but as long as you have
[tt]
A B C D
Yeovil 1 3 4
Yeovil, Somerset 0 2 2
Yeovil - Somerset 7 1 8[/tt]

in different columns, then use the SumProduct formula I provided. The "ISNUMBER((SEARCH(" acts as a wildcard entry, looking for any occurrence of Yeovil, regardless of what else is in the cell.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[dallen43]

A Pivot Table would work for that.

Deb

 

[KenWright]

I'd be down Skip's route, and would cleanse the data first

Unique list of whats in Col A using Data / Filter / Adv Filter, then sort that to group like entries. Copy the data so that you have two identical columns of data and then edit the righthand one, changing 'Yeovil - Somerset' to Yeovil etc, so you end up with unique values original and edited side by side.

Now just add a field to your data and VLOOKUP the original place, bringing in the new place.

Then throw a Pivot table at it, or whatever else you want to do.

Either way you have manual labour to do, so you may as well bite the bullet, knuckle down and end up with a good set of data.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------