Is this possible with excel ?

[ChrisB]

I have two tab delimited spreadsheet files of a product database. My
MAIN concern here is that I need to import the prices from one
spreadsheet, to a specific column in my original spreadsheet, and I
need those prices to correspond with the respective product IDs. Is
this possible ??? thanks in advance guys!

 

[Dave O]

It's likely very possible, but we'd need to see some sample data so we
know what data points are available to work with. Please post sample
data.

 

[ChrisB]

It's likely very possible, but we'd need to see some sample data so we
know what data points are available to work with. Please post sample
data.

Ok... My main spreadsheet (tab delimited file) I have it setup as
follows:

A B C
D

ProductID49483 Brief Description Image.jpg 3.00
ProductID93094 Brief Description Image.jpg 5.00
ProductID30940 Brief Description Image.jpg 8.00

my alternate spreadsheet (tab delimited file) is as follows:

A B

ProductID49483 4.50
ProductID93094 8.50
ProductID30940 9.50


I want to make it so that I can Import my alternate spreadsheet into my
main spreadsheet, but I only want it to alter column D on my main
spreadsheet (changing prices). I need a macro, or a formula which will
import column B from my alternate spreadsheet into column D in my main
spreadsheet. I also need this macro/formula to be able to correspond
the price with the correct productID...so it changes the correct
product to its designated price.

Any Ideas guys???

 

[ChrisB]

I received an #N/A error. I think this might be on the right track to
helping me....can you elaborate a bit more? I am referencing the main
spreadsheet as alternate, and using A:G for the table array because A
is what i need it to reference in the main spreadsheet and G is the
prices on the alternate, using col_index_num 7 as that is the column
number, all with your formula and im receiving that error.

 

[ChrisB]

First off let me thank you for the detailed post with steps on how to
achieve and overcome my problem. I appreciate it more than you know
Secondly, I have followed your steps to the T, and am getting this #N/A
error. When I ask for help on the error, this is what excel says to me:

Correct a #N/A error
Occurs when a value is not available to a function or formula.

Click the cell that displays the error, click the button that appears ,
and then click Trace Error if it appears.
Review the possible causes and solutions.
Possible causes and solutions
Missing data, and #N/A or NA() has been entered in its place

Replace #N/A with new data.

Note You can enter #N/A in those cells where data is not yet
available. Formulas that refer to those cells will then return #N/A
instead of attempting to calculate a value.

Giving an inappropriate value for the lookup_value argument in the
HLOOKUP, LOOKUP, MATCH, or VLOOKUP worksheet function

Make sure the lookup_value argument is the correct type of value- for
example, a value or a cell reference, but not a range reference.
Using the VLOOKUP, HLOOKUP, or MATCH worksheet function to locate a
value in an unsorted table

By default, functions that look up information in tables must be sorted
in ascending order. However, the VLOOKUP and HLOOKUP worksheet
functions contain a range_lookup argument that instructs the function
to find an exact match even if the table is not sorted. To find an
exact match, set the range_lookup argument to FALSE.
The MATCH worksheet function contains a match_type argument that
specifies the order the list must be sorted in to find a match. If the
function cannot find a match, try changing the match_type argument. To
find an exact match, set the match_type argument to 0.

Using an argument in an array formula that is not the same number of
rows or columns as the range that contains the array formula

If the array formula has been entered into multiple cells, make sure
the ranges referenced by the formula have the same number of rows and
columns, or enter the array formula into fewer cells. For example, if
the array formula has been entered into a range 15 rows high (C1:C15)
and the formula refers to a range 10 rows high (A1:A10), the range
C11:C15 will display #N/A. To correct this error, enter the formula
into a smaller range (for example, C1:C10), or change the range to
which the formula refers to the same number of rows (for example,
A1:A15).

Omitting one or more required arguments from a built-in or custom
worksheet function

Enter all arguments in the function.

Using a custom worksheet function that is not available

Make sure the workbook that contains the worksheet function is open and
the function is working properly.

Running a macro that enters a function that returns #N/A

Make sure the arguments in the function are correct and in the correct
position.

 

[ChrisB]

I have a spreadsheet titled "memory" which holds about 600 products.
that spreadsheets columns are as follows: A (product_ID) , B (empty) ,
C (product description) , D (product_img), E (product_price) , F
(product_name), G (category).

My Alternate spreadsheet titled "pricelist" which holds the CORRECT
prices but also about 9,000 products...I need to import into my
"memory" spreadsheet are as follows: A (product_id), B
(manufacturer_product_id) , C (manufacturer) , D (category) , E (type),
F (price) , G (in stock or not) , H (Weight).


I made sure both the product ids were in the leftmost column (A) , and
also in ascending order before continuing with the formula. I am pretty
positive none of the ids are repeating.

 

[Guest]

Thanks for the info. O.k. I made a sample of your spreadsheet (tabs names
and column headings, also filled in sample product IDs and prices) with the
information that you provided. Just to make sure this works for me..On the
sample spreadsheet on tab - memory I inserted a new column to the left of E
(product_price) to put the Vlookup into...then used the following formula:
=VLOOKUP(A2,pricelist!A:F,6,FALSE)

It worked and pulled across the corresponding price for the first product
ID...just remember if it works for you to copy/paste special values so that
you keep the new prices and not the formula!! I'll be here for about 45
more minutes hope it works!!

 

[ChrisB]

I copied everything to a flash drive to try to work on this over the
weekend. Maybe something is wrong within my spreadsheet, Im gonna try
to find a way to show you parts of my spreadsheet, there might be
something wrong with it.`

 

[Guest]

I'm so sorry it just occurred to me that you are not working with 2 tabs in
one workbook but 2 completely seperate files all together. I do apologize -
this is what happens when you muli-task. If that is the case then the
vlookup would look like this:
=VLOOKUP(A2,pricelist.xls!$A:$F,6,FALSE)

just add ".xls" to the end of pricelist
just to be certain make sure to have both files open

 

[ChrisB]

I got it to work!!! GREAT!! thanks so much.... Now is it possible to
get it automated so it filters through the entire list? or do I have to
do this on my own ?