Excel Help

StonerStuart said:
Whats the value the vlookup is supposed to return?
Click to expand...

The range OP is looking for is between cat and cat, from what I can make out. Then dog to dog. And I assume others in between. OP can concatenate a VLOOKUP result with a unique identifier for each and then create a formula to calculate between the unique identifiers. All it basically does is allow it all to exist in one column as opposed to various columns. Although if column A is the only criteria that needs to be searched then your count should work to define the number of cells, with which the OP can then use to create a decent formula.

Must say, I've never had to work with such an ugly set of data before, so not entirely sure how to accomplish what the OP is after, let alone the OP being rather vague about it...
 
DJ... said:
The range OP is looking for is between cat and cat, from what I can make out. Then dog to dog. And I assume others in between. OP can concatenate a VLOOKUP result with a unique identifier for each and then create a formula to calculate between the unique identifiers. All it basically does is allow it all to exist in one column as opposed to various columns. Although if column A is the only criteria that needs to be searched then your count should work to define the number of cells, with which the OP can then use to create a decent formula.

Must say, I've never had to work with such an ugly set of data before, so not entirely sure how to accomplish what the OP is after, let alone the OP being rather vague about it...
Click to expand...

he cant use vlookup because this data set is confusing even me...

is it 12 mice the cat ate?

OP youll get no help anywhere esp excel help forums they will delete your thread for lack of clarity!

Like i said supply the information and phrase your request correctly!
 
If the "cat" and "dog" text filled the blanks then you could simply use a sumifs formula, it is a sumif formula but with multiple criterias.

So if A1 to A5 had the text cat then column A would be your first criteria range and first criteria would be cat then your second criteria range would be whatever is in column c with a unique criteria and then sum range is the range where the values are that you want to see.
 
Pivot table should be able to do that. Just play around with the headings/columns
 
Okay guys the actual data is received from caseware in such a matter. Im not able to post the actual data for obvious reasons. The format I gave is how I received the data. MY APOLOGIES for posting this vaguely.
I have add some headings maybe this would help?
Untitled.jpg

Im using Excel 2010! I have already written vba function and using that as formula in the cells on the sheet i'm doing the summary and it works great.

Just pondering if their would have been simpler solution. Thanks for everyone's help!
 
What are you summing between cat and cat, and what purpose does column B serve?
 
DJ... said:
What are you summing between cat and cat, and what purpose does column B serve?
Click to expand...

The mouse and bird in this instance is the staff member's id code.

ATM im using the following code and then just adding it as function:

Code: 
Function sumData(ByVal search)

 Dim rngFindStart As Range
 Dim rngFindEnd As Range
    Dim Starttempint As Integer
     Dim Endtempint As Integer
    Set rngFindStart = ActiveSheet.Columns("A:A").Find(What:=search, After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
     If Not rngFindStart Is Nothing Then
      Starttempint = rngFindStart.Row + 1
      
      End If
         
  Set rngFindEnd = ActiveSheet.Columns("A:A").Find(What:=search, After:=Range("A" & Starttempint & ""), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
     If Not rngFindEnd Is Nothing Then
      Endtempint = rngFindEnd.Row - 1
       sumData = WorksheetFunction.Sum(Range("D" & "" & Starttempint & "" & ":D" & "" & Endtempint & ""))
      End If
    
End Function

seems to be working just fine.
 
As per your example

ScreenHunter_11 Mar. 09 13.34.jpg

Copy formula in E2 and paste it down in column E. You can then hide column E and use function SUMIF() to telly the numbers
 
Last edited:
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X