Alpha/Numerical numbers

  • Thread starter Thread starter rhani111
  • Start date Start date
R

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
 
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 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
 
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
 
Would you rather not use a pivot table for this type of task? This will
group all items (no duplicates) with grand totals etc..
 
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
 
Back
Top