SOLVED

Excel searching, matching and editing information from one table to another

Copper Contributor

Hello, everyone! This is my first discussion here and I apologize in advance if I have not met any condition or requirement for this publication.

It is difficult for me to realize the following, and I would like to do it:

 

1) I have a table with 5 columns and 10.000 rows

- the columns names are: number; name; price; delivery time; weight;

2) I have a second table with the same columns like the first, but with changes in one of the two columns - price and delivery time

3) How can the second table be made to be checked by the first or more precisely by the number of the first column?

- if there is a match from the second table with the first table(table 1, row 1 with number 123 = table 2, row 5 with number 123) -> compare price and delivery time between this match. If price from second table is 5$ and in the 1st is 3$ -> change the price in 1st table to 5$

 

I read a lot in this forum, but I never managed to cope. I'm asking for help. I hope you understand me. Thank you in advance!

 

6 Replies

@bardukiss 

 

Is it possible for you to upload or post a copy of your spreadsheet? If not the actual--if it contains private or proprietary information--perhaps a mockup that resembles it.

 

If not, then you'll need to answer a few questions which seeing the actual would circumvent.

 

Are the "numbers" in each row--the "123" in your example--actual unique identifiers for the rest of the data? If so, you should be able to use VLOOKUP or one of the similar functions to do the comparison. Then an IF function could be used to change the prices in the first sheet to reflect those in the second.

 

It would help us help you, though, if you could post a copy of your actual sheet.

@mathetes

Thank you for your answer! I've attached both of tables and in the second I've marked the changes with yellow. Can you check it, please. Thank you!

best response confirmed by bardukiss (Copper Contributor)
Solution

@bardukiss 

 

Here are two possible solutions for you. The first will work with older versions of Excel, for it uses a formula to check the (potentially) older prices and delivery times against the new that looks like this:

=IF(INDEX(Table1[Price],MATCH([@Partnumber],Table1[Partnumber],0))<>C2,INDEX(Table1[Price],MATCH([@Partnumber],Table1[Partnumber],0)),C2)

 

The heart of that formula is the INDEX/MATCH that retrieves the price from the more recent table ("Table1").

INDEX(Table1[Price],MATCH([@Partnumber],Table1[Partnumber],0))

The IF function then compares that price to the old price  <>C2

and, if they are unequal,

inserts the new price, INDEX(Table1[Price],MATCH([@Partnumber],Table1[Partnumber],0)),

but otherwise retains the old price. C2)

 

The second solution uses a much newer function, LET, that makes it possible to shorten the formula above by only using the INDEX/MATCH formulation a single time, defining a short term value to be the result of that formulation. I'll let you research the LET function on your own--there are various on-line resources that explain it better than I could. If it works for you, I highly recommend learning how to use it, as it can save lots of time and reduce errors in long formulas.

=LET(

AccPrc,INDEX(Table1[Price],MATCH([@Partnumber],Table1[Partnumber],0)),

IF(AccPrc<>C2,AccPrc,C2)

)

LET allows us to define "AccPrc" as the result of looking up the price, using INDEX and MATCH, in the table that contains the more recent data.

It then uses that "Accurate Price" in the IF formula to compare with the old price and change it to the new if they're not the same.

 

Then, in both sample workbooks, I used Conditional Formatting to highlight the rows in which the data have been changed.

 

If you have further questions, please don't hesitate to write back. This is not meant to be the end result....you will probably want to format things differently; I did it like this to make visible the components.

@mathetes 

 

Lol, I never imagined that such a thing could be done in excel like this way. Thank you, fantastic job! I like how it works, but after 1 month for example, when I have to enter a new prices after since I have already had a change in the column "Adjstd Price", it will compare the primary prices in column "Price" again and in reality this column becomes redundant. I hope you understand what I mean or you made this solution only for comparing the prices from both tables and I should ignore the "Price" column for future, or?

 

 

@bardukiss 

 

How you take it to the next step is very  much up to you, based on your understanding of where the "new prices" come from, how they get compared, what then happens with the new revised.  

 

For example, if you want to see the update and then make a judgment to make that the new "official price" I would suggest you update the "Price" column. That way the next month you'd have a fresh comparison.  But I can conceive of a case where you (or management) might split the difference between the old and the new. 

 

My goal was simply to show how the prices can be compared and a result both delivered and highlighted. 

@mathetes 

 

Thank you for your help and explanation! That was everything! If there is something else which I am interested in, I will open a new discussion.

1 best response

Accepted Solutions
best response confirmed by bardukiss (Copper Contributor)
Solution

@bardukiss 

 

Here are two possible solutions for you. The first will work with older versions of Excel, for it uses a formula to check the (potentially) older prices and delivery times against the new that looks like this:

=IF(INDEX(Table1[Price],MATCH([@Partnumber],Table1[Partnumber],0))<>C2,INDEX(Table1[Price],MATCH([@Partnumber],Table1[Partnumber],0)),C2)

 

The heart of that formula is the INDEX/MATCH that retrieves the price from the more recent table ("Table1").

INDEX(Table1[Price],MATCH([@Partnumber],Table1[Partnumber],0))

The IF function then compares that price to the old price  <>C2

and, if they are unequal,

inserts the new price, INDEX(Table1[Price],MATCH([@Partnumber],Table1[Partnumber],0)),

but otherwise retains the old price. C2)

 

The second solution uses a much newer function, LET, that makes it possible to shorten the formula above by only using the INDEX/MATCH formulation a single time, defining a short term value to be the result of that formulation. I'll let you research the LET function on your own--there are various on-line resources that explain it better than I could. If it works for you, I highly recommend learning how to use it, as it can save lots of time and reduce errors in long formulas.

=LET(

AccPrc,INDEX(Table1[Price],MATCH([@Partnumber],Table1[Partnumber],0)),

IF(AccPrc<>C2,AccPrc,C2)

)

LET allows us to define "AccPrc" as the result of looking up the price, using INDEX and MATCH, in the table that contains the more recent data.

It then uses that "Accurate Price" in the IF formula to compare with the old price and change it to the new if they're not the same.

 

Then, in both sample workbooks, I used Conditional Formatting to highlight the rows in which the data have been changed.

 

If you have further questions, please don't hesitate to write back. This is not meant to be the end result....you will probably want to format things differently; I did it like this to make visible the components.

View solution in original post