Alpha/Numerical numbers
 

Hi all,

I am trying to match data in a formula and the cell contains an alpha
numeric value....with spaces...ughhhh

e.g. VK 1234 RAM which is in A2

What I need to retrieve is lookup the value in A2:A6, find exact match
to that value and ADD the totals of each value...

e.g.

A B C
VK 1234 RAM 18/07/06 2
VK 1234 RAM 20/07/06 5
VK 5678 RAM 18/07/06 3
VK 1234 RAM 18/07/06 5

RESULT I'm after would be

VK 1234 RAM 10
VK 5678 RAM 5

Can anyone help me?


--
rhani111
------------------------------------------------------------------------
rhani111's Profile: http://www.excelforum.com/member.php...o&userid=19940
View this thread: http://www.excelforum.com/showthread...hreadid=565847

Alpha/Numerical numbers
 

are you saying that ' VK 1234 RAM 18/07/06 2 ' is all in one cell and if
so, which cell would you want the totals to appear in?

John


--
johncassell
------------------------------------------------------------------------
johncassell's Profile: http://www.excelforum.com/member.php...o&userid=25016
View this thread: http://www.excelforum.com/showthread...hreadid=565847

Alpha/Numerical numbers
 

just seen your abc bit

in that case if you stick this formula in cell d2...

=A2& " "&SUMIF(A:A,A2,C:C)

this will result in VK 1234 RAM 10


--
johncassell
------------------------------------------------------------------------
johncassell's Profile: http://www.excelforum.com/member.php...o&userid=25016
View this thread: http://www.excelforum.com/showthread...hreadid=565847

Alpha/Numerical numbers
 

OMG....that is almost perfect....i say ALMOST because I need the VK 1234
RAM in one column and the total (10) in the next column....

Please please help remove the text from the sum result......

Tar muchly
Sandi


--
rhani111
------------------------------------------------------------------------
rhani111's Profile: http://www.excelforum.com/member.php...o&userid=19940
View this thread: http://www.excelforum.com/showthread...hreadid=565847

Alpha/Numerical numbers
 

d2 formula: =a2
e2 formula: =SUMIF(A:A,A2,C:C)


--
johncassell
------------------------------------------------------------------------
johncassell's Profile: http://www.excelforum.com/member.php...o&userid=25016
View this thread: http://www.excelforum.com/showthread...hreadid=565847

Alpha/Numerical numbers
 

okies.....i had worked that one out myself after i posted thankx.....

Due to there being more than one sheet with the same item codes etc
(for each different site that I record for) how do i stop it listing
the duplicates, but only list it once with a total for all?


--
rhani111
------------------------------------------------------------------------
rhani111's Profile: http://www.excelforum.com/member.php...o&userid=19940
View this thread: http://www.excelforum.com/showthread...hreadid=565847

Alpha/Numerical numbers
 

Would you rather not use a pivot table for this type of task? This will
group all items (no duplicates) with grand totals etc..


--
johncassell
------------------------------------------------------------------------
johncassell's Profile: http://www.excelforum.com/member.php...o&userid=25016
View this thread: http://www.excelforum.com/showthread...hreadid=565847

Alpha/Numerical numbers
 

No unfortunately, I can't use them......ughhhhh


--
rhani111
------------------------------------------------------------------------
rhani111's Profile: http://www.excelforum.com/member.php...o&userid=19940
View this thread: http://www.excelforum.com/showthread...hreadid=565847

Alpha/Numerical numbers
 

Is that 'can't use them because I don't think I'm clever enough' or
'can't use them because of some other reason??! :-)

Well here's the answer for both:
1. Formula version...

cell D2 formula: =IF(COUNTIF($A$1:A2,A2)1,"",A2)
*notice that A1 is absolute i.e it does not change but A2 is not so
when you drag it down it will change to $A$1:A3, $A$1:A3 etc..

cell E2 formula: =IF(D2="","",SUMIF(A:A,A2,C:C))

2. Pivot Table...
Make sure there are column headers in cells A1 to C1

Pick a cell where you would like to see the table..
On the menu go to DataPivot Table and PivotChart.

click next and then select ranges A to C so in the box it looks
something like this: Sheet1!$A:$C

click next

click layout

drag the word in cell A1 into the row box
drag the word in cell C1 into the data box

that box will say 'count of...' - double click this and select SUM.

click OK then finish.

Hope this covers everything!!

John


--
johncassell
------------------------------------------------------------------------
johncassell's Profile: http://www.excelforum.com/member.php...o&userid=25016
View this thread: http://www.excelforum.com/showthread...hreadid=565847