Thread: 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?

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

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

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

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

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?

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

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

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:A4 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 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