Excel 2007 : Copy last number from a list

[pansovic]

Attached an Excel doc where is explained what I need.

[Richard Buttrey]

Hi,

Use a helper column and in D2 enter

Please Login or Register  to view this content.

and in E2

Please Login or Register  to view this content.

then copy D2:E2 down

Rgds

[pansovic]

Thanks a lot for your great reply. It helped me a lot but yet not the final answer. Attached I send you my observation. The cell in yellow doesn't give me the right value as that I have added a new line in line7

[DonkeyOte]

Using the 2nd sample file - do you perhaps mean:

Please Login or Register  to view this content.

[pansovic]

Sorry, maybe I have not made myself clear. Idea is as follow:
PURCHASE: I just fill in a number in column C which is the unit price of the product that I buy
ADJUSTMENT: In column C> nothing happens. I don't need to calculate anything, neither do I fill in a number
LABELLING: Here I need a formula in Column C which gives me the latest purchase price. So in E8 need to get number 18 (from C5), in cell E9 number 26 (from C6), in cell E12 number 9 (from C10)

[DonkeyOte]

LABELLING: Here I need a formula in Column C which gives me the latest purchase price. So in E8 need to get number 18 (from C5), in cell E9 number 26 (from C6), in cell E12 number 9 (from C10)

Crystal Clear... presumably you mean Col E when you say you need a formula for Column C.

The solution provided previously* is for Column E and returns the values you state above.
*modify delimiters etc per your locale

Please note that Vista is not an Office version (it's an operating system) - I suspect you are using Office (XL)2007 - please update your profile accordingly.

[pansovic]

1. Excel "Vista" updated. Thanks
2. Delimeters are adjusted. You use "," but I have to use ";"
3. I do in fact need for C but is not really important as I always can adjust. It is just to give you the picture of the difference between Purchase, Adjustement and Labelling:
When column A "Purchase" I fill in a number in column C
When column A "Adjustment" I don't fill in anything in column C (this is important)
When column A "Labelling" I need the formula taking me the last number of Purchase from a specific product.

The formula you gave me is that it takes always the last number of a specific product but as that I don't fill in a number with "Adjustement" it gives me a "0" when I use same product. So, I only need the last number whern it is "Purchase".

[DonkeyOte]

The formula you gave me is that it takes always the last number of a specific product but as that I don't fill in a number with "Adjustement" it gives me a "0" when I use same product.

Post a sample file to demonstrate the above.

The formula uses the last price found in Column E not C (ie it uses prior results) - therefore the fact that Adjustment is 0 in Col C is irrelevant (E will be the last price)

[pansovic]

We are confused.
Attach I send you Excel doc with

1. in column E your formula
   =IF(ISNUMBER($C2),$C2,LOOKUP(2,1/($B$1:$B1=$B2),$E$1:$E1))

1. In column F your other formula
   =INDEX(C:C,MATCH(B2&COUNTIF($B$2:B2,B2)-1,$D$1:D2,0),1). With this formula you in fact were closer to the final result though F8 has to be "18" and not "0".

Attach I send you as well an image of what I see. This is in spanish and maybe there is an issue of translation. For example I don't understand "1/"
The first image shows the formula used in column F. Second image shows formula used in column E

• =SI(ESNUMERO($C8);$C8;LOOKUP(2;1/($B$1:$B7=$B8);$E$1:$E7))
  means
  =IF(ISNUMBER($C8),$C8,LOOKUP(2,1/($B$1:$B7=$B8),$E$1:$E7))

• #żNOMBRE? means #NAME?

• =INDICE(C:C;COINCIDIR(B2&CONTAR.SI($B$2:B2;B2)-1;$D$1:D2;0);1)
  means
  =INDEX(C:C,MATCH(B2&COUNTIF($B$2:B2,B2)-1,$D$1:D2,0),1)

[DonkeyOte]

LOOKUP translation would be BUSCAR

The #NAME? error results from LOOKUP being an invalid reference - ie is not seen as a function and is deemed to be a named range (which does not exist)