**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

i am doing this link

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

https://drive.google.com/file/d/1CmdDjfLVCWYpMhcD2ppBewFa1ZqM7ZCd/view?usp=sharing

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.*