Thread
:
Sumproduct to return a text
View Single Post
#
6
Posted to microsoft.public.excel.worksheet.functions
Sandy Mann
external usenet poster
Posts: 2,345
Sumproduct to return a text
Would a User Defined Function do? If so with the list of Account No's &
Profit Center's in A1:B6, the list of Groups, Account No's and Profit
Centers (both with ******), in G1:I5, I created another table in K1:L5,
(labels in K1&L1) with the formula:
=SUBSTITUTE(H2,"*","")
and copied down & across.
The UDF in a normal module is:
Option Explicit
Function FindIt(Acc, Ctr)
Dim x As Long
Dim Here As String
Application.Volatile
For x = 2 To 5 'Change to suit your list
If Left(Acc, Len(Cells(x, 11).Value)) = _
Cells(x, 11) Then
If Left(Ctr, Len(Cells(x, 12).Value)) = _
Cells(x, 12) Then
Here = Cells(x, 7).Value
Exit For
End If
End If
Next x
FindIt = Here
End Function
Then in C2 I entered the formula:
=FindIt(A2,B2)
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
Replace @mailinator.com with @tiscali.co.uk
"Chanceuxbp" wrote in message
...
Hi Sandy, yes there are more account numbers and Group. The following is
an
extract. There would be account 22000 that falls into another Group
22*******. That's why I have 210000 in the Group 21******.
Basically, it is to use lookup value (Account+Profit Center) in Sheet1
with
lookup array in Sheet2 to return a Group Name to Sheet1 column C.
Please help.
Thank you.
"Sandy Mann" wrote:
What I was meaning was if 31000 is in the same group as 30000 why would
21000 not be in the same 20000? Or if it is why 21*******? why not just
2********?
You say that there are more account numbers, are there more Groups?
--
Regards,
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
Replace @mailinator.com with @tiscali.co.uk
"Chanceuxbp" wrote in message
...
Yes, there will be more accounts numbers.
The group name to appear on Sheet 1 if both account numbers and profit
center on Sheet 1 match both account numbers and profit center on Sheet
2.
3***** means account number starting with 3 and 8***** means any profit
centers starting with 8 will meet the conditions to return the Group
Name
on
Sheet2 to Sheet1 Column C(Group)
21****** means account number starting with 21 and 400**** means any
profit
centers starting with 400 will meet the conditions to return the Group
Name
on Sheet2 to Sheet 1 Column C(Group).
Hope that I am clearer now.
Thank you.
"Sandy Mann" wrote:
I think that you are going to have to provide more details of how it
is
decided what appears in Sheet 2 eg why 3*********** and 8*********
but
21******* & 400********? Additionally I assume that there will be
more
account numbers.
For what you posted:
=IF(ISNUMBER(SEARCH(SUBSTITUTE(Sheet2!$B$2,"*","") ,$A$2:$A$4)),"A","B")
and copied down would return A, B & A but I suspect that that s not
what
you
want.
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
Replace @mailinator.com with @tiscali.co.uk
"Chanceuxbp" wrote in message
...
I have a 2 worksheets:
Sheet 1:
Account No. Profit Center Group
31000 80000000
21000 40000000
30000 85000000
I want to return the Group Name from another worksheet containing
wildcard
data:
GROUP Account No Profit Center
A 3*********** 8***********
B 21********** 400********
For e.g the first record in Sheet 1 to return Group A, 2nd record to
return
Group B and third record to return Group A.
I try sumproduct but can only return value not text.
Please help.
Reply With Quote
Sandy Mann
View Public Profile
Find all posts by Sandy Mann