help if and macros

M

marcelo2112

New Member
Joined
Dec 1, 2014
Messages
15
I got a list of clients that have food restriction like this:[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]Client[/TD]
[TD]Restriction1[/TD]
[TD]Restriction2[/TD]
[TD]Restriction3[/TD]
[TD]Restriction4[/TD]
[TD]Restriction5[/TD]
[/TR]
[TR]
[TD]Zed[/TD]
[TD]Rice[/TD]
[TD]Pasta[/TD]
[TD]Fish[/TD]
[TD]Fried Food[/TD]
[TD]Apples[/TD]
[/TR]
[TR]
[TD]Charles[/TD]
[TD]Rice[/TD]
[TD]Fish[/TD]
[TD]Fried Food[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
And a list of foods that I serve in my company like this:

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]Food[/TD]
[TD]ingredient1[/TD]
[TD]ingredient2[/TD]
[TD]ingredient3[/TD]
[TD]ingredient4[/TD]
[TD]ingredient5[/TD]
[/TR]
[TR]
[TD]Food1[/TD]
[TD]Rice[/TD]
[TD]Pasta[/TD]
[TD]Fish[/TD]
[TD]Fried Food[/TD]
[TD]Apples[/TD]
[/TR]
[TR]
[TD]Food2[/TD]
[TD]Pasta[/TD]
[TD]Meat[/TD]
[TD]Fried Food[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]food3[/TD]
[TD]Cheese[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I wanted to know if is it possible to make a table for instance or of the number of people that cant eat food1 or food2, or what persons cant eat food1 or food2, or food3

Is a big emergency!!
Thanks for the help
 
Sort by date Sort by votes
marcelo2112,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


3. What are the actual worksheet names?


Sample raw data worksheets:


Excel 2007
ABCDEF
1ClientRestriction1Restriction2Restriction3Restriction4Restriction5
2ZedRicePastaFishFried FoodApples
3CharlesRiceFishFried Food
4Carl
5
Clients



Excel 2007
ABCDEF
1Foodingredient1ingredient2ingredient3ingredient4ingredient5
2Food1RicePastaFishFried FoodApples
3Food2PastaMeatFried Food
4food3Cheese
5
Foods




We can not tell where your raw data is located, cells, rows, columns, and, we can not tell where the results should be, cells, rows, columns.


So that we can get it right on the first try:

Can you post screenshots of the actual raw data worksheets?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot try one of the following:

Excel Jeanie
Download

MrExcel HTML Maker
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


Or, you can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.


Is a big emergency!!
Click to expand...

4. What is the purpose of this request/exercise?

5. Is this a homework assignment?
 
Last edited:
Upvote 0
I'm going to assume that the first list you provided is located in Sheet1, and the second list that you provided is located in Sheet2. I am going to output results in Sheet3. And since you gave no means of how to input data, I'm going to improvise.
Code: 
Sub myMacro()
    sht1 = "Sheet1"  'Clients and restriction sheet
    sht2 = "Sheet2"  'Food and ingrediant sheet
    sht3 = "Sheet3"  'Output sheet
    lastRow = Sheets(sht3).Range("A" & Rows.Count).End(xlUp).Row
    If lastRow > 1 Then
        Sheets(sht3).Range("A2:A" & lastRow).ClearContents
    End If
    lastRow = Sheets(sht3).Range("B" & Rows.Count).End(xlUp).Row
    If lastRow > 1 Then
        Sheets(sht3).Range("B2:B" & lastRow).ClearContents
    End If
    myInputValue = InputBox("You want to output the names of all the people who can and cannot eat the food you type in this box")  'type food1 into this box for example.
    lastRow = Sheets(sht2).Range("A" & Rows.Count).End(xlUp).Row
    firstRow = 2  'First row of data in sht2
    i = firstRow
    foundValueBool = FALSE
    Do Until i > lastRow
        If Sheets(sht2).Range("A" & i).Value = TRIM(myInputValue) Then
            foundValueBool = TRUE    
            Exit Do
        End If
        i = i + 1
    Loop
    If foundValueBool = FALSE Then
        MsgBox "Could not find " & myInputValue & " in the list of foods"
        Exit Sub
    End If
    lastColumn = Sheets(sht2).Cells(i, Columns.Count).End(xlToLeft).Column
    firstColumn = 2  'foods begin in column B
    c = firstColumn
    Dim arrayFoods as Variant
    a = -1
    Do Until c > lastColumn
        a = a + 1
        ReDim Preserve arrayFoods(a)
        arrayFoods(a) = Sheets(sht2).Cells(i, c).Value
        c = c + 1
    Loop
    a = 2  'output of sht3 will begin on row 2.  If they can eat the food
    b = 2  'output of sht3 will begin on row 2.  If they can NOT eat the food
    lastRow = Sheets(sht1).Range("A" & Rows.Count).End(xlUp).Row
    firstRow = 2  'First row of data in sht1 starts on row 2
    i = firstRow
    Do Until i > lastRow
        CanEatBoolean = TRUE
        lastColumn = Sheets(sht1).Cells(i, Columns.Count).End(xlToLeft).Column
        If lastColumn > 1 Then
            firstColumn = 2  'First column of food data in sht1 is in column 2
            c = firstColumn
            Do Until c > lastColumn
                For Each item in arrayFoods
                    If item = Sheets(sht1).Cells(i, c).Value Then
                        CanEatBoolean = FALSE
                        Exit Do
                    End If
                Next item
                c = c + 1
            Loop
        End If
        If CanEatBoolean = TRUE Then
            Sheets(sht3).Range("A" & a).Value = Sheets(sht1).Range("A" & i).Value
            a = a + 1
        Else
            Sheets(sht3).Range("B" & b).Value = Sheets(sht1).Range("B" & i).Value
            b = b + 1
        End If
        i = i + 1
    Loop
End Sub
If they can eat the food you designate, it will output their name in sht3 column A.
If they can NOT eat the food you designate, it will output their name in sht3 column B.
So after you run the code, you can count how many can eat the food or how many can NOT eat the food by using a countIF function. If you need help with that easy function then let us know.
 
Last edited:
Upvote 0
I used MSQuery which is part of Excel and created the following query. I put each item in a table that was normalized as shown below on sheets 1 and 2.
Excel Workbook
AB
1NameRestriction
2ZedRice
3ZedPasta
4ZedFish
5ZedFried Food
6ZedApples
7CharlesRice
8CharlesFish
9CharlesFried Food
Sheet1


Excel Workbook
AB
1FoodIngredient
2Food1Rice
3Food1Pasta
4Food1Fish
5Food1Fried Food
6Food1Apples
7Food2Pasta
8Food2Meat
9Food2Fried Food
10Food3Cheese
Sheet2

the result looks like this
Excel Workbook
AB
1NameFood
2CharlesFood1
3CharlesFood2
4ZedFood1
5ZedFood2
Sheet4






Here is the SQL statement

SELECT DISTINCT `Sheet1$`.Name, `Sheet2$`.Food
FROM `C:\Users\Alan Sidman\Desktop\Book12.xlsx`.`Sheet1$` `Sheet1$`, `C:\Users\Alan Sidman\Desktop\Book12.xlsx`.`Sheet2$` `Sheet2$`
WHERE `Sheet1$`.Restriction = `Sheet2$`.Ingredient
 
Upvote 0
Hi!
Thanks for the help!

I work in a home for elderly people and I need this information so I can now for instance how many or wich people can eat the meal 1 and who can not, and then for meal 2 and for meal 3, etc.

I am working on excel 2011 for mac but I have excel 2013 in windows if is easier for you all.

I send you the link with the workbook:

https://www.dropbox.com/s/byco9ofibo3d3at/Alimentação.xlsx?dl=0

please note that the ingredients of the food/restriction and food names and people are in portuguese because it is my native language.

Thanks again for the help!
 
Upvote 0
Hi!
Thanks for the help!

I work in a home for elderly people and I need this information so I can now for instance how many or wich people can eat the meal 1 and who can not, and then for meal 2 and for meal 3, etc.

I am working on excel 2011 for mac but I have excel 2013 in windows if is easier for you all.

I send you the link with the workbook:

https://www.dropbox.com/s/byco9ofibo...%A3o.xlsx?dl=0

please note that the ingredients of the food/restriction and food names and people are in portuguese because it is my native language.

Thanks again for the help!
 
Upvote 0
Hi!
Thanks for the help!

I work in a home for elderly people and I need this information so I can now for instance how many or wich people can eat the meal 1 and who can not, and then for meal 2 and for meal 3, etc.

I am working on excel 2011 for mac but I have excel 2013 in windows if is easier for you all.

I send you the link with the workbook:

https://www.dropbox.com/s/byco9ofibo...%A3o.xlsx?dl=0

please note that the ingredients of the food/restriction and food names and people are in portuguese because it is my native language.

Thanks again for the help!

WarPiglet said:
I'm going to assume that the first list you provided is located in Sheet1, and the second list that you provided is located in Sheet2. I am going to output results in Sheet3. And since you gave no means of how to input data, I'm going to improvise.
Code: 
Sub myMacro()
    sht1 = "Sheet1"  'Clients and restriction sheet
    sht2 = "Sheet2"  'Food and ingrediant sheet
    sht3 = "Sheet3"  'Output sheet
    lastRow = Sheets(sht3).Range("A" & Rows.Count).End(xlUp).Row
    If lastRow > 1 Then
        Sheets(sht3).Range("A2:A" & lastRow).ClearContents
    End If
    lastRow = Sheets(sht3).Range("B" & Rows.Count).End(xlUp).Row
    If lastRow > 1 Then
        Sheets(sht3).Range("B2:B" & lastRow).ClearContents
    End If
    myInputValue = InputBox("You want to output the names of all the people who can and cannot eat the food you type in this box")  'type food1 into this box for example.
    lastRow = Sheets(sht2).Range("A" & Rows.Count).End(xlUp).Row
    firstRow = 2  'First row of data in sht2
    i = firstRow
    foundValueBool = FALSE
    Do Until i > lastRow
        If Sheets(sht2).Range("A" & i).Value = TRIM(myInputValue) Then
            foundValueBool = TRUE    
            Exit Do
        End If
        i = i + 1
    Loop
    If foundValueBool = FALSE Then
        MsgBox "Could not find " & myInputValue & " in the list of foods"
        Exit Sub
    End If
    lastColumn = Sheets(sht2).Cells(i, Columns.Count).End(xlToLeft).Column
    firstColumn = 2  'foods begin in column B
    c = firstColumn
    Dim arrayFoods as Variant
    a = -1
    Do Until c > lastColumn
        a = a + 1
        ReDim Preserve arrayFoods(a)
        arrayFoods(a) = Sheets(sht2).Cells(i, c).Value
        c = c + 1
    Loop
    a = 2  'output of sht3 will begin on row 2.  If they can eat the food
    b = 2  'output of sht3 will begin on row 2.  If they can NOT eat the food
    lastRow = Sheets(sht1).Range("A" & Rows.Count).End(xlUp).Row
    firstRow = 2  'First row of data in sht1 starts on row 2
    i = firstRow
    Do Until i > lastRow
        CanEatBoolean = TRUE
        lastColumn = Sheets(sht1).Cells(i, Columns.Count).End(xlToLeft).Column
        If lastColumn > 1 Then
            firstColumn = 2  'First column of food data in sht1 is in column 2
            c = firstColumn
            Do Until c > lastColumn
                For Each item in arrayFoods
                    If item = Sheets(sht1).Cells(i, c).Value Then
                        CanEatBoolean = FALSE
                        Exit Do
                    End If
                Next item
                c = c + 1
            Loop
        End If
        If CanEatBoolean = TRUE Then
            Sheets(sht3).Range("A" & a).Value = Sheets(sht1).Range("A" & i).Value
            a = a + 1
        Else
            Sheets(sht3).Range("B" & b).Value = Sheets(sht1).Range("B" & i).Value
            b = b + 1
        End If
        i = i + 1
    Loop
End Sub
If they can eat the food you designate, it will output their name in sht3 column A.
If they can NOT eat the food you designate, it will output their name in sht3 column B.
So after you run the code, you can count how many can eat the food or how many can NOT eat the food by using a countIF function. If you need help with that easy function then let us know.
Click to expand...
 
Upvote 0
Hi!
Thanks for the help!

I work in a home for elderly people and I need this information so I can now for instance how many or wich people can eat the meal 1 and who can not, and then for meal 2 and for meal 3, etc.

I am working on excel 2011 for mac but I have excel 2013 in windows if is easier for you all.

I send you the link with the workbook:

https://www.dropbox.com/s/byco9ofibo...%A3o.xlsx?dl=0

please note that the ingredients of the food/restriction and food names and people are in portuguese because it is my native language.

Thanks again for the help!

hiker95 said:
marcelo2112,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


3. What are the actual worksheet names?


Sample raw data worksheets:

Excel 2007
ABCDEF

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]Client[/TD]
[TD="bgcolor: #FFFFFF"]Restriction1[/TD]
[TD="bgcolor: #FFFFFF"]Restriction2[/TD]
[TD="bgcolor: #FFFFFF"]Restriction3[/TD]
[TD="bgcolor: #FFFFFF"]Restriction4[/TD]
[TD="bgcolor: #FFFFFF"]Restriction5[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]Zed[/TD]
[TD="bgcolor: #FFFFFF"]Rice[/TD]
[TD="bgcolor: #FFFFFF"]Pasta[/TD]
[TD="bgcolor: #FFFFFF"]Fish[/TD]
[TD="bgcolor: #FFFFFF"]Fried Food[/TD]
[TD="bgcolor: #FFFFFF"]Apples[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]Charles[/TD]
[TD="bgcolor: #FFFFFF"]Rice[/TD]
[TD="bgcolor: #FFFFFF"]Fish[/TD]
[TD="bgcolor: #FFFFFF"]Fried Food[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF"]Carl[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Clients​



Excel 2007
ABCDEF

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]Food[/TD]
[TD="bgcolor: #FFFFFF"]ingredient1[/TD]
[TD="bgcolor: #FFFFFF"]ingredient2[/TD]
[TD="bgcolor: #FFFFFF"]ingredient3[/TD]
[TD="bgcolor: #FFFFFF"]ingredient4[/TD]
[TD="bgcolor: #FFFFFF"]ingredient5[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]Food1[/TD]
[TD="bgcolor: #FFFFFF"]Rice[/TD]
[TD="bgcolor: #FFFFFF"]Pasta[/TD]
[TD="bgcolor: #FFFFFF"]Fish[/TD]
[TD="bgcolor: #FFFFFF"]Fried Food[/TD]
[TD="bgcolor: #FFFFFF"]Apples[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]Food2[/TD]
[TD="bgcolor: #FFFFFF"]Pasta[/TD]
[TD="bgcolor: #FFFFFF"]Meat[/TD]
[TD="bgcolor: #FFFFFF"]Fried Food[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF"]food3[/TD]
[TD="bgcolor: #FFFFFF"]Cheese[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Foods​





We can not tell where your raw data is located, cells, rows, columns, and, we can not tell where the results should be, cells, rows, columns.


So that we can get it right on the first try:

Can you post screenshots of the actual raw data worksheets?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot try one of the following:

Excel Jeanie
Download

MrExcel HTML Maker
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


Or, you can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.




4. What is the purpose of this request/exercise?

5. Is this a homework assignment?
Click to expand...
 
Upvote 0
I am sending you a link for my workbook, and can you please adapt the code to it? I tried but I cant.

https://www.dropbox.com/s/byco9ofibo3d3at/Alimentação.xlsx?dl=0

thanks a lot!

And sorry for the amount of equal replys!

WarPiglet said:
I'm going to assume that the first list you provided is located in Sheet1, and the second list that you provided is located in Sheet2. I am going to output results in Sheet3. And since you gave no means of how to input data, I'm going to improvise.
Code: 
Sub myMacro()
    sht1 = "Sheet1"  'Clients and restriction sheet
    sht2 = "Sheet2"  'Food and ingrediant sheet
    sht3 = "Sheet3"  'Output sheet
    lastRow = Sheets(sht3).Range("A" & Rows.Count).End(xlUp).Row
    If lastRow > 1 Then
        Sheets(sht3).Range("A2:A" & lastRow).ClearContents
    End If
    lastRow = Sheets(sht3).Range("B" & Rows.Count).End(xlUp).Row
    If lastRow > 1 Then
        Sheets(sht3).Range("B2:B" & lastRow).ClearContents
    End If
    myInputValue = InputBox("You want to output the names of all the people who can and cannot eat the food you type in this box")  'type food1 into this box for example.
    lastRow = Sheets(sht2).Range("A" & Rows.Count).End(xlUp).Row
    firstRow = 2  'First row of data in sht2
    i = firstRow
    foundValueBool = FALSE
    Do Until i > lastRow
        If Sheets(sht2).Range("A" & i).Value = TRIM(myInputValue) Then
            foundValueBool = TRUE    
            Exit Do
        End If
        i = i + 1
    Loop
    If foundValueBool = FALSE Then
        MsgBox "Could not find " & myInputValue & " in the list of foods"
        Exit Sub
    End If
    lastColumn = Sheets(sht2).Cells(i, Columns.Count).End(xlToLeft).Column
    firstColumn = 2  'foods begin in column B
    c = firstColumn
    Dim arrayFoods as Variant
    a = -1
    Do Until c > lastColumn
        a = a + 1
        ReDim Preserve arrayFoods(a)
        arrayFoods(a) = Sheets(sht2).Cells(i, c).Value
        c = c + 1
    Loop
    a = 2  'output of sht3 will begin on row 2.  If they can eat the food
    b = 2  'output of sht3 will begin on row 2.  If they can NOT eat the food
    lastRow = Sheets(sht1).Range("A" & Rows.Count).End(xlUp).Row
    firstRow = 2  'First row of data in sht1 starts on row 2
    i = firstRow
    Do Until i > lastRow
        CanEatBoolean = TRUE
        lastColumn = Sheets(sht1).Cells(i, Columns.Count).End(xlToLeft).Column
        If lastColumn > 1 Then
            firstColumn = 2  'First column of food data in sht1 is in column 2
            c = firstColumn
            Do Until c > lastColumn
                For Each item in arrayFoods
                    If item = Sheets(sht1).Cells(i, c).Value Then
                        CanEatBoolean = FALSE
                        Exit Do
                    End If
                Next item
                c = c + 1
            Loop
        End If
        If CanEatBoolean = TRUE Then
            Sheets(sht3).Range("A" & a).Value = Sheets(sht1).Range("A" & i).Value
            a = a + 1
        Else
            Sheets(sht3).Range("B" & b).Value = Sheets(sht1).Range("B" & i).Value
            b = b + 1
        End If
        i = i + 1
    Loop
End Sub
If they can eat the food you designate, it will output their name in sht3 column A.
If they can NOT eat the food you designate, it will output their name in sht3 column B.
So after you run the code, you can count how many can eat the food or how many can NOT eat the food by using a countIF function. If you need help with that easy function then let us know.
Click to expand...
 
Upvote 0
It doesn't matter what language it is in. I will not apply the code for you. It is basic knowledge that you should know. Go to YouTube and search for "Unlock Developer Tab". Then search for "Adding VBA code to module". That will show you how to apply the code.
 
Upvote 0
You must log in or register to reply here.

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top