Combine Data in excel

Microsoft Excel 2003 (full product)
March 11, 2010 at 10:01:37
Specs: Windows XP
I have a spreadsheet with two columns.
Column A contains Account Names and
Column B contains the number of service
request they've opened. Column A has similar
Account names in separate rows and would
like to combine these in one row. For
instance, column a will have a name like
Joe's Coffee on one row, then Joes coffee, ltd
on another. I would like these to be on one
row with the total value in B from both. Is this

See More: Combine Data in excel

Report •

March 11, 2010 at 10:25:16
re: I would like these to be on one row with the total value in B from both

If the 2 similar "Joe's Coffee" entries are in Column A, how would you put them in the same Row?

I see 2 options: combine them into 1 cell in Column A or put them in a different Column in the same Row.

In other words, they can't both be in the same Row in Column A - unless they are in the same cell.

Even after you answer that question, I think it's going to take some VBA using the Like operator to compare similar names. However, even that will be troublesome if you have entries such as:

Joe's Coffee
Joes Coffee
Coffee Shack

Deciding what string to compare to find "matches" could be an interesting exercise.

It may be a little late now, but it would be better if you created a "contact list" and use some type of Data Validation to prevent the entry of slightly different names for the same establishment.

Report •

March 11, 2010 at 12:02:31

Automating the merge process might merge some of the wrong records.

You could use a semi-automated process.

Create a master list of the correct or 'approved' account names (you will be able to use this in the future as DerbyDad03 says, for data entry validation).

Have the names in a column somewhere on the same worksheet, say column F.

Now insert a new column to the right of the original Account names. This new column will be column B.

Select all the cells in this column alongside the existing account names in column A.
Right-click this selection and from the Data menu select Validation...
In the validation dialog box, in the Settings tab, select List in the 'Allow:' drop-down
In the 'Source:' box enter the range of cells containing your 'approved' names.
Click OK.

Now go down your list of account names and in the new column B, use the drop-down in each cell to select the 'approved' name for the name used in column A.

Once this is done it will be easy to combine all the data for each account.
Next to your list of approved names (if the list starts in cell F2), enter this formula in cell G2
where $B$2:$B$100 is the range of cells with the selected approved names and $C$2:$C$100 is the column with the service requests - adjust the ranges as appropriate.

Drag the formula down to the bottom of your list of approved names. You will now have the totals for each name.


Report •

Related Solutions

Ask Question