Sumproduct to return a text
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. |
Sumproduct to return a text
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. |
Sumproduct to return a text
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. |
Sumproduct to return a text
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. |
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. |
All times are GMT +1. The time now is 01:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com