Replacing information

J

Jenn

I have been sent a distribution list which includes columns for marke
name, publication name, store chain, store name, and address. I nee
to add a text column that will fill in a title automatically, dependin
upon what the market name and publication name are.

For example, the list may include:
Market = Baltimore / Publication = Baltimore Guide
Market = Baltimore / Publication = Washington Guide
Market = Washington / Publication = Washington Guide

For each entry, I need to add a column for the actual publicatio
title; Title = The Greater Baltimore Area Guide
or Title = The Greater Washington Area Guide

There are about 37,000 entries.
There are about 100 Titles.

How can I set up a spreadsheet to automatically choose the correc
title from a list? i.e. If Market = A and Publication = B, then Titl
= C

If this is unclear, please let me know.
Thank you for your help
 
F

Frank Kabel

Hi
if I understood you correctly try the following formula (Assumptions:
col. A=Market name; col. B = publication name) in C1
="The Greater " & A1 & MID(B1,FIND(" ",B1)+1,255)
and copy down
 
O

Otto Moehrbach

Jenn
Here is a small macro that will do what you want. The assumptions that
fit with this macro are:
1. You have a sheet named "Table".
2. In this sheet you have all the markets listed in Column A starting
with A2 and going down.
3. In this sheet you have all the publications listed in Row 1 starting
with B1 and going to the right.
4. You have this table filled in with all the possible publication
titles.
5. In your data sheet you have Market Name, Pub Name, Store Chain,
Address, and Title in Columns A:F. The Market Name and Pub Name must be in
Columns A & B respectively.
6. Your data sheet must be the active sheet when you run this macro.
7. The macro will put the titles in Column F.
Note that there are no error traps built-in to this macro. It is assumed
that every market and publication listed in the data sheet is included in
the Table sheet.
Please post back if you need any more. HTH Otto
Sub GetTitles()
Dim MarketRng As Range, TableCityRng As Range, TablePubRng As Range
Dim i As Range, FoundRow As Long, FoundCol As Long
Application.ScreenUpdating = False
Set MarketRng = Range("A2", Range("A" & Rows.Count).End(xlUp))
With Sheets("Table")
Set TableCityRng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
Set TablePubRng = .Range("B1", .Range("IV1").End(xlToLeft))
For Each i In MarketRng
FoundRow = TableCityRng.Find(What:=i, LookAt:=xlWhole).Row
FoundCol = TablePubRng.Find(What:=i(, 2), LookAt:=xlWhole).Column
i(, 6) = .Cells(FoundRow, FoundCol)
Next i
End With
Application.ScreenUpdating = True
End Sub
 
Top