Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Excel Wizards?


velo1ever

Recommended Posts

I have been looking on the net for a bit, and I don't even know what to type in for a search, that is how clueless I am...

If any excel wizards can help me, please, please, let me know....

I have a list of items I have on one sheet, on the second sheet, I have the items I carry, I am looking for a formula(S), that will carry over the items that are in stock from sheet 1 to sheet 2, so that I then can upload via easypopulate to my store. (Basically saving time from searching each product individually to ensure product is available to sell), as I am not carrying all the products from sheet 1..just a selection.. :'(

Does any of that make sense?!? If so, you can PM me or just post here...

Again, thank you!!!!!

Link to comment
Share on other sites

I have been looking on the net for a bit, and I don't even know what to type in for a search, that is how clueless I am...

If any excel wizards can help me, please, please, let me know....

I have a list of items I have on one sheet, on the second sheet, I have the items I carry, I am looking for a formula(S), that will carry over the items that are in stock from sheet 1 to sheet 2, so that I then can upload via easypopulate to my store. (Basically saving time from searching each product individually to ensure product is available to sell), as I am not carrying all the products from sheet 1..just a selection.. :'(

Does any of that make sense?!? If so, you can PM me or just post here...

Again, thank you!!!!!

Yes it makes sense.

 

You have to have to use the VLOOKUP function.

You have to have the same key (model number probably) in both sheets.

The key is assumed to be the first column in sheet 2.

The key MUST be the first column in sheet 1

Sheet 1 should be sorted alphabetically, otherwise you can get strange results

It is assumed you have columns A through Z on sheet 1, but that is not that important, it just has to include the column you want data from.

 

the formula gets the 4th column, and assumes you have 1000 rows.

 

in sheet 2 in a new column you create the formula to VLOOKUP(A1;SHEET1!$A$1:$Z$1000;4;FALSE)

 

Note, I assume ; is your field delimiter in formulas, depending on your installation, this might be different.

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Link to comment
Share on other sites

another trick I sometimes use, find the INSERT product line in EP and comment it out.

then upload your complete file, if it finds it, it will be update, if not, nothing is added, so no harm;

then just uncomment the line again afterwards.

the output from EP is misleading, but otherwise it works as a quick hack.

 

I was thinking of adding this as an EP option to have this behaviour as default, and having to explicitly say inserts are allowed ...

well, so much todo, so many ideas, so very few hours in a day...

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Link to comment
Share on other sites

Yes it makes sense.

 

You have to have to use the VLOOKUP function.

You have to have the same key (model number probably) in both sheets.

The key is assumed to be the first column in sheet 2.

The key MUST be the first column in sheet 1

Sheet 1 should be sorted alphabetically, otherwise you can get strange results

It is assumed you have columns A through Z on sheet 1, but that is not that important, it just has to include the column you want data from.

 

the formula gets the 4th column, and assumes you have 1000 rows.

 

in sheet 2 in a new column you create the formula to VLOOKUP(A1;SHEET1!$A$1:$Z$1000;4;FALSE)

 

Note, I assume ; (semi-colon) is your field delimiter in formulas, depending on your installation, this might be different and would then probably a , (comma)

 

In excel in dutch the function is called VERT.ZOEKEN

 

In openoffice you have to use other delimiters for sheet.cell references in particular the ! needs to be .

 

so the dutch open office calc equivalent is: VERT.ZOEKEN(A1;SHEET1.$A$1:$Z$1000;4;FALSE)

 

the $ is the same in excel and calc, and is used to fix the lookup area so you can copy the formula across rows and columns.

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Link to comment
Share on other sites

If you still need help, I've written a lot of VB Scripts to transfer data within spreadsheets, and from one spreadsheet file to another.

 

PM me if you want help.

;)

If I suggest you edit any file(s) make a backup first - I'm not perfect and neither are you.

 

"Given enough impetus a parallelogramatically shaped projectile can egress a circular orifice."

- Me -

 

"Headers already sent" - The definitive help

 

"Cannot redeclare ..." - How to find/fix it

 

SSL Implementation Help

 

Like this post? "Like" it again over there >

Link to comment
Share on other sites

If you still need help, I've written a lot of VB Scripts to transfer data within spreadsheets, and from one spreadsheet file to another.

 

PM me if you want help.

;)

Germ, do you have similar skills in openoffice too ?

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Link to comment
Share on other sites

Sorry, Ma'am.... I've never worked with OpenOffice...

:blush:

If I suggest you edit any file(s) make a backup first - I'm not perfect and neither are you.

 

"Given enough impetus a parallelogramatically shaped projectile can egress a circular orifice."

- Me -

 

"Headers already sent" - The definitive help

 

"Cannot redeclare ..." - How to find/fix it

 

SSL Implementation Help

 

Like this post? "Like" it again over there >

Link to comment
Share on other sites

too bad, I've never programmed VB like in openoffice either, but my excelplugin for osfinancials wont run on vista, so I though if it needs changing why not try the openoffice way ...

anyhow, I'll just see if I can get calc talking to firebird using odbc, nice project for the weekend

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Link to comment
Share on other sites

As office 2007 has been done with some open xml format.

 

Also PHPexcel has some classes(with some limitation) it is possibloe to read an xls sheet(should not be too complicated) to be used in PHP.

 

 

 

Satish

Ask/Skype for Free osCommerce value addon/SEO suggestion tips for your site.

 

Check My About US For who am I and what My company does.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...