merge data, eliminating data, replacing cell number data with text string.

**JimExcel123** · 04-28-2017, 10:40 PM

I have a spread sheet I am trying cleanup, then to migrate to a newer one with data in one field being changed to a text string.

I am hoping to do it the simplest way.

Spreadsheet "VersionA" contains 6300 rows, lots are no longer active
has maybe 20 columns 2 of which are crucial. Part number (sku) and shipping cost in $xx.xx format
the shipping only consists of 19 variations of numbers ( 19 pricing levels)
Not all the rows will be needed to be merged into the new file.

Spreadsheet "Item list" contains 3300 only the active skus

1st I want to purge the inactive skus.

then
The Spreadsheet target "VersionB"
It has about 36 columns
I want to merge the active skus into this while change the pricing levels to a text string.
example
$3.30 becomes "0.50 lb shipping cost" in the field shipping level
$4.90 becomes "0.95 lb shipping cost" in the field shipping level

...
I though of doing a global search and replace in the numbers to the text string, but one of the #.## might also happen to be part of a product sku like ####.###.##, plus some of the text strings to be change into, have shipping cost levels in the text string like AAAA6.99AAAAA8.99
_______
Later , After the sheet "VersionB" is completed (and used separately, ), I will merge the shipping cost data into 15 different sheets based upon category. Each sheet will have between 50 to 500 specific sku's in them .

Any suggestions?

**Glenn Kennedy** · 04-29-2017, 03:09 AM

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

**JimExcel123** · 04-30-2017, 08:28 PM

correction, the shipping cost field is called "shippingamt1"

there was a third file, but it was 2.5 mb it exceeded the file limit, and when I zipped it to be 2.4 mb it failed to upload. the limit the site stated is 9 mb for zips