# Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

i need help to understand this If Left(rngArr(i, j), 1) <> "P"
Solved by S. A. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
User 22/11/2017 - 09:07
hi
Excelchat Expert 22/11/2017 - 09:07
Hello there, welcome to Got It Pro
User 22/11/2017 - 09:08
i just to understand this formula
User 22/11/2017 - 09:08
If Left(rngArr(i, j), 1) <> "P"
Excelchat Expert 22/11/2017 - 09:08
This is VBA?
User 22/11/2017 - 09:08
yes
Excelchat Expert 22/11/2017 - 09:09
rngArr is an array, with 2 dimensions
Excelchat Expert 22/11/2017 - 09:09
rngArr(i,j) will retrieve an element in the Array, which is a string
Excelchat Expert 22/11/2017 - 09:10
Left(rngArr(i,j),1) will then extract a single Character from the retrieve string
Excelchat Expert 22/11/2017 - 09:10
If Left(rngArr(i,j),1) <> "P"
User 22/11/2017 - 09:11
and check if it is not equal to P?
Excelchat Expert 22/11/2017 - 09:11
checks whether the extracted Character is not equal to "P"
Excelchat Expert 22/11/2017 - 09:11
Yes
User 22/11/2017 - 09:11
what if i want the same formula but instead of not equal to P, i want it to check if the string is not available in a group of cells
User 22/11/2017 - 09:12
string = Blue, Cells = Yellow & Red
User 22/11/2017 - 09:12
so it will not match
Excelchat Expert 22/11/2017 - 09:13
Then you need to loop through the cells and check if there is no match
User 22/11/2017 - 09:13
my cells are from A1 to A107
Excelchat Expert 22/11/2017 - 09:13
Lets assume the cells were in the range A1:A107
User 22/11/2017 - 09:13
how can i write the formula?
Excelchat Expert 22/11/2017 - 09:13
wait
Excelchat Expert 22/11/2017 - 09:13
dim rng as Range
Excelchat Expert 22/11/2017 - 09:14
set rng = ActiveSheet.Range("A1:A107")
Excelchat Expert 22/11/2017 - 09:14
dim cell as Range
Excelchat Expert 22/11/2017 - 09:14
for each cell in rng
User 22/11/2017 - 09:15
so i should write these 3 lines?
Excelchat Expert 22/11/2017 - 09:15
yes I am not finished
User 22/11/2017 - 09:15
oh ok
Excelchat Expert 22/11/2017 - 09:15
what do you want to do if the value is found?
Excelchat Expert 22/11/2017 - 09:15
if there is a match
User 22/11/2017 - 09:16
i have the other formula ready
User 22/11/2017 - 09:16
in vba
User 22/11/2017 - 09:16
i just want this match
Excelchat Expert 22/11/2017 - 09:16
what should the program do if there was a match?
User 22/11/2017 - 09:16
If cnt = nmbr Then viceversa = rngArr(i, j) Exit Function Else cnt = cnt + 1 End If Else temp = viceversa(rngArr(i, j), rng, nmbr, True) If temp <> "" Then viceversa = temp Exit Function End If End If Next j End If Next i viceversa = "" End Function
User 22/11/2017 - 09:17
this
User 22/11/2017 - 09:17
it is working i just wanted the match
User 22/11/2017 - 09:17
dim rng as Range set rng = ActiveSheet.Range("A1:A107") dim cell as Range for each cell in rng
User 22/11/2017 - 09:18
these are them right?
Excelchat Expert 22/11/2017 - 09:18
yes
User 22/11/2017 - 09:18
okay thanks alot
Excelchat Expert 22/11/2017 - 09:19
You need to wrap this around your matching part
Excelchat Expert 22/11/2017 - 09:19
for each cell in rng
Excelchat Expert 22/11/2017 - 09:19
....
Excelchat Expert 22/11/2017 - 09:19
....
Excelchat Expert 22/11/2017 - 09:19
next cell
Excelchat Expert 22/11/2017 - 09:19
Then for the matching its
Excelchat Expert 22/11/2017 - 09:20
If Left(rngArr(i,j),1) <> cell.vale
Excelchat Expert 22/11/2017 - 09:20
If Left(rngArr(i,j),1) <> cell.value
User 22/11/2017 - 09:21
If Left(rng(i,j),1) <> cell.value
User 22/11/2017 - 09:21
not this?
Excelchat Expert 22/11/2017 - 09:21
OK, just an example
User 22/11/2017 - 09:21
and i want it to match the whole string not just the first character
Excelchat Expert 22/11/2017 - 09:22
Are you getting your values from the array?
User 22/11/2017 - 09:22
yes
Excelchat Expert 22/11/2017 - 09:22
if rngArr(i, j) <> cell.value
User 22/11/2017 - 09:23
ok
User 22/11/2017 - 09:23
i will try
User 22/11/2017 - 09:25
what if the cells are in Sheet1?
Excelchat Expert 22/11/2017 - 09:26
set rng=Sheet1.range("A1:A107")
User 22/11/2017 - 09:28
rng As Range
User 22/11/2017 - 09:28
duplicate declaration in scope
User 22/11/2017 - 09:28
how can i user another name
Excelchat Expert 22/11/2017 - 09:29
rng is just a variable, you can replace with anything you want;
Excelchat Expert 22/11/2017 - 09:29
dim myRange as Range
Excelchat Expert 22/11/2017 - 09:29
set myRange=Sheet1.Range("A1:A107")
User 22/11/2017 - 09:30
okay
User 22/11/2017 - 09:31
invalid Next Control variable reference
User 22/11/2017 - 09:31
NExt j
Excelchat Expert 22/11/2017 - 09:31
send me code so I check
User 22/11/2017 - 09:33
Dim cnt As Long Function viceversa(lkup As Variant, rng As Range, nmbr As Long, Optional rec As Boolean) As String Dim rngArr Dim temp As String Dim rng2 As Range Set rng2 = Sheet1.Range("A1:A107") Dim cell As Range If rec = False Then cnt = 1 rngArr = rng.Value For i = LBound(rngArr, 1) To UBound(rngArr, 1) If rngArr(i, 1) = lkup Then For j = LBound(rngArr, 2) + 1 To UBound(rngArr, 2) For Each cell In rng2 If rngArr(i, j) <> cell.Value Then If cnt = nmbr Then viceversa = rngArr(i, j) Exit Function Else cnt = cnt + 1 End If Else temp = viceversa(rngArr(i, j), rng, nmbr, True) If temp <> "" Then viceversa = temp Exit Function End If End If Next j End If Next i viceversa = "" End Function
User 22/11/2017 - 09:33
https://stackoverflow.com/questions/34422994/how-to-list-ingredients-for-a-product-if-one-product-can-have-another-product-as
User 22/11/2017 - 09:33
Excelchat Expert 22/11/2017 - 09:37
try this
Excelchat Expert 22/11/2017 - 09:37
Dim cnt As Long Function viceversa(lkup As Variant, rng As Range, nmbr As Long, Optional rec As Boolean) As String Dim rngArr Dim temp As String Dim rng2 As Range Set rng2 = Sheet1.Range("A1:A107") Dim cell As Range If rec = False Then cnt = 1 rngArr = rng.Value For i = LBound(rngArr, 1) To UBound(rngArr, 1) If rngArr(i, 1) = lkup Then For j = LBound(rngArr, 2) + 1 To UBound(rngArr, 2) For Each cell In rng2 If rngArr(i, j) <> cell.Value Then If cnt = nmbr Then viceversa = rngArr(i, j) Exit Function Else cnt = cnt + 1 End If Else temp = viceversa(rngArr(i, j), rng, nmbr, True) If temp <> "" Then viceversa = temp Exit Function End If End If next cell Next j End If Next i viceversa = "" End Function
User 22/11/2017 - 09:38
can you tell me what you changed becuase i could not copy and paste it
Excelchat Expert 22/11/2017 - 09:38
before the next j, I added
Excelchat Expert 22/11/2017 - 09:38
next cell
User 22/11/2017 - 09:40
the code worked but it did not do what i want
User 22/11/2017 - 09:40
and i am unable to attach it to you
Excelchat Expert 22/11/2017 - 09:40
You cannot attach macro file. You have to put it like on google drive or dropbox and share the link
Excelchat Expert 22/11/2017 - 09:41
what do you want it to do
Excelchat Expert 22/11/2017 - 09:41
?
User 22/11/2017 - 09:42
User 22/11/2017 - 09:42
here it is
User 22/11/2017 - 09:43
i word for a restauart, he have this excel, he wants to know each product what ingredients it have, in details, because some ingredients are products that have ingredients, we want all to be ingredients
User 22/11/2017 - 09:45
Function viceversa(lkup As Variant, rng As Range, nmbr As Long, Optional rec As Boolean) As String Dim rngArr Dim temp As String If rec = False Then cnt = 1 rngArr = rng.Value For i = LBound(rngArr, 1) To UBound(rngArr, 1) If rngArr(i, 1) = lkup Then For j = LBound(rngArr, 2) + 1 To UBound(rngArr, 2) If Left(rngArr(i, j), 1) <> "P" Then If cnt = nmbr Then viceversa = rngArr(i, j) Exit Function Else cnt = cnt + 1 End If Else temp = viceversa(rngArr(i, j), rng, nmbr, True) If temp <> "" Then viceversa = temp Exit Function End If End If Next j End If Next i viceversa = "" End Function
User 22/11/2017 - 09:45
this code was returning the names as is, and not fetching if the name is a product about its dependent ingredients to write them also
Excelchat Expert 22/11/2017 - 09:50
You want it to also show Yeast Raised Concentrate, Water, etc from B2 going down on sheet3
User 22/11/2017 - 09:51
for example for Yeast mix it should show me
User 22/11/2017 - 09:51
Plain Flour Yeast Raised Concentrate Water Dry Yeast Pack*450 Gr Soya Oil
Excelchat Expert 22/11/2017 - 09:51
It should show below Yeast?
Excelchat Expert 22/11/2017 - 09:52
Yeast Mix
User 22/11/2017 - 09:52
yes
User 22/11/2017 - 09:52
for L1 in sheet3, that only contains yeast , i dont want to see yeast, i want it to show me also
User 22/11/2017 - 09:52
Plain Flour Yeast Raised Concentrate Water Dry Yeast Pack*450 Gr Soya Oil
User 22/11/2017 - 09:52
the ingredients of yeast
User 22/11/2017 - 09:53
so in summary if it have ingredients i want them, if ingredients and products i want them all as ingredients
User 22/11/2017 - 10:01
is it possible?
Excelchat Expert 22/11/2017 - 10:02
Yes but takes a bit of time, still looking at the code
User 22/11/2017 - 10:02
oh okay, the last code i sent you is working almost 50% the trick is
User 22/11/2017 - 10:03
One thing that will need to change is the test of whether it is a product or not. So change this line If Left(rngArr(i, j), 1) <> "P" Then to something that works to denote that the ingredient being tested is actually a product.
Excelchat Expert 22/11/2017 - 10:04
You will need to repost, not enough time left
User 22/11/2017 - 10:07
oh

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.