eCommerce Tycoon

**mikej2009** · 04-09-2010, 01:34 PM

database help for my business with excel!?
I have a few problems.

basically i run a small business, the products i buy from the distributor/manufacturer i need updated once a week.

the update comes from a master list the distributor sends out, but this contains over 20,000 of their products and services.

i have only a few hundred i need updated. I need items updated such as pricing, location, etc.

the "rows" in excel represent the products and the columns are fields such as price, location and others.

the products are never in the same row. so i am looking for software or any means to update my datasheet with the new prices, locations, etc. automatically according to the product name.

so if i have product "A" and it is 2499.99 and then it goes to 2348.98, i need to auto update that with the master sheet once a week.

the other problem i see is often i need to add or take away form everything in a whole row or column,

if all of column b has the city name{s} of cities in florida for example, and they are just listed like this.

orlando. miami. pensacola. , how would i add in the phrase { , florida} to the whole column so that it shows orlando, florida miami, florida, etc.?

these are the kind of solutions i need to find to manage my business better, any advice would be great, im not to much looking for excel formulas as much as i am an "addon" "extention" or software solution but will do what is necessary.

**Chianti** · 04-09-2010, 02:20 PM

It seems to me that your main problem is that you're trying to make excel work like a database. It isn't. It is a calculator. A big, massive calculator. Using it as a database of products is like using Word (instead of powerpoint) for a presentation.

I would:

· use formula to extract only your products from the master
· try importing the excel sheet into 'MS Access' and use a database for your queries.
· ask the company to provide you with an Access file instead of excel.
· tell the company your problems and ask for their suggestions.

Personally, I've never managed to wrap my head around Access, and have never been motivated to learn it because I've never needed to make database queries like that. You may have a long learning curve ahead with Access.

I'd be surprised if an excel plugin solved your problem.

**mikej2009** · 04-09-2010, 05:13 PM

well if i need to use access that is fine.

i need ANY solution, i just use excel for a .csv file to be updated and the distributor is a .xls file.

**Chianti** · 07:01 PM

I achieved what you need by using: excel -> data -> filter -> advanced filter

What you need to do is copy the identifying info (product number) of your 'few hundred' products into it's own column on sheet1 of a new workbook, with the correct heading that matches the master sheet:

column A
1 product no <---- (or whatever is used on the master sheet)
2 23475 <---- these are the products you sell
3 25857
4 29485
5 14873
6 39384
7 26897
8 36734
.
.
203 68795

For the advanced filter to work you need to put an = in front of these.
To do this, put this formula in cell B2:

="="&A2&""

The cell B2 should now say =23475
Drag the right bottom corner of this cell all the way down to the bottom of your list. All the product numbers will will now have = appended. Move the title/heading (product number) to B1.

Now you copy/paste your master into sheet2
Then select it all, and go to the advanced filter.
The first box is selected already with the range of the master sheet.
The second box, you go and select your list of products on sheet1.
You click 'copy to another location' in the dialogue box, and go select A1 on sheet3.

When you click ok, sheet 3 becomes your new updated data sheet.
It will only have your products listed.

Hope that helps.

**Chianti** · 07:12 PM

Quote:

if all of column b has the city name{s} of cities in florida for example, and they are just listed like this.

orlando. miami. pensacola. , how would i add in the phrase { , florida} to the whole column so that it shows orlando, florida miami, florida, etc.?

Say this is column E
1 location
2 orlando
3 miami
4 pensacola

Insert new column to the right (a new, blank column F) by clicking the top of column F and right-click -> 'insert'

In the new column, in F2 put =E2&", florida"
The cell F2 should now say orlando, florida
Drag the bottom right corner of the cell down the entire list.
Select the new column. Right-click copy.
Select E2. Paste Special and choose 'values' instead of all.
Column E now has the desired new text.
Column F looks a bit funky now, but don't worry. Delete column F (click the top -> del).

All this stuff looks complicated when you're trying to follow it, but once you've done it a couple of times, I reckon you have have your data sheet updated in a few minutes.