Alpha/Numerical numbers

[rhani111]

Hi all,

I am trying to match data in a formula and the cell contains an alph
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 matc
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

 

[johncassell]

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]

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

 

[rhani111]

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

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

Tar muchly
Sand

 

[johncassell]

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

 

[rhani111]

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 et
(for each different site that I record for) how do i stop it listin
the duplicates, but only list it once with a total for all

 

[johncassell]

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

 

[rhani111]

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

 

[johncassell]

Is that 'can't use them because I don't think I'm clever enough' o
'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 s
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 Data>Pivot Table and PivotChart.

click next and then select ranges A to C so in the box it look
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!!

Joh