Need Help creating an IF THEN scenario [Solved/Closed]

Question

Previous Topic Next Topic

Need Help creating an IF THEN scenario [Solved/Closed]

Report

blakerwilson

Posts: 4
Registration date: Friday April 28, 2017
Status: Member
Last seen: May 3, 2017

- May 1, 2017 at 12:03 PM
vcoolio

Posts: 1309
Registration date: Thursday July 24, 2014
Status: Moderator
Last seen: July 22, 2021

- May 10, 2017 at 08:28 PM

Hello,

I am looking for some help with a problem. I have a big list of items within Excel and I am wanting to consolidate the big list of items down to a small set of items my department will handle.

What I am looking to do is similar to an IF/THEN statement where IF I put an 'X' in any cell in column 'B', THEN Excel will take the entire content of the row and populate the data of that row in a different sheet.

Let's say I wanted to start a zoo and I had a list of all the different types of animals available, I want to be able to go through that list and simply put that 'X' in column B and have the rest of the information about that animal show up on a separate sheet so I can look as if I know WAY more about the animal than I really do...

COLUMN A would be the list of animals.
COLUMN B would be my area to put the X to identify That is the animal I want in my Zoo
COLUMNS C-E would be all the supporting information about these animals.

I am happy to provide all of the made up data if someone would be willing to tell me how I would bend Excel to my need.

I am new to this and would be very happy to put this sample Excel doc out on a Google Drive or some sort of cloud drive if it is needed.

Thanks!
Blake

Need Help creating an IF THEN scenario
Create email account yahoo - How-To - Yahoo mail
State information about number and size of partition created - Articles
How to create a list on snapchat - How-To - Snapchat
How to create shortcut in messenger - How-To - Facebook
Create a new gmail account by mobile number - How-To - Gmail

4 replies

Ask a question

Blocked Profile · Answer 1 · 2017-05-01T16:58:22-0400

Answer 1 / 4

Blocked Profile
May 1, 2017 at 04:58 PM

The if statement syntax is as follows:
=IF(logic_test,true,false)

So, with that in mind and considering Cell A1="hi".

If we have in cell B1:
=if(A1="hi","the word hi is there","some other word is there")
...would produce "the word hi is there" in cell B1

SO, use that as an example, and have a go. You can nest if then statments, as in:
=IF(logic_test#1,if(LogicTest#2if#1IsTrue,"TRUE FOR BOTH","true for one not for two"),"false for one")
I hope this helps.

Have a go!

Report

blakerwilson

Posts: 4
Registration date: Friday April 28, 2017
Status: Member
Last seen: May 3, 2017

May 2, 2017 at 01:11 PM

I was thinking more along the lines of implementing something like a VLOOKUP scenario in which the new sheet would look in a specific column on the MASTER SHEET for something like an 'X' and if it saw an 'X', it would put the information from the specified cell into that new worksheet. I understand that I would need to put that VLOOKUP formula into each cell in order to get the information you need but I am having problems with getting the formula to work correctly and I am stumped. I may have led you astray with the idea of an IF statement and for that I am sorry...

Report

Blocked Profile
May 2, 2017 at 04:42 PM

if you need help combining functions, let us know. Could use AND, If and Vlookup. Let us know!

Have fun!

Report

blakerwilson

Posts: 4
Registration date: Friday April 28, 2017
Status: Member
Last seen: May 3, 2017

May 2, 2017 at 05:15 PM

here is a link to my fake document... Please let me know if it helps fill in what I am trying to do.
https://drive.google.com/file/d/0B8ZUqlS0WQfUV21PVVpyR2hJQWc/view?usp=sharing

vcoolio · Answer 2 · 2017-05-03T04:19:21-0400

Hello Blake,

You could try a macro code for this also. Assign the following code to a button and see if it does the task for you. The code is untested so test it in a copy of your work book first.

Sub TransferData()

Dim lr As Long

Application.ScreenUpdating = False

Sheet1.Range("A1", Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp)).AutoFilter 1, "X", 7
  lr = Sheet1.Range("B" & Rows.Count).End(xlUp).Row
    If lr > 1 Then
      Sheet1.Range("B2", Sheet1.Range("E" & Sheet1.Rows.Count).End(xlUp)).Copy
        Sheet2.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
             Sheet2.Columns.AutoFit
         End If
Sheet1.[A1].AutoFilter

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

On clicking on the button, the code filters Column A on sheet 1 for the criteria "X" (as per your sample) and will then transfer the relevant rows of data (from Column B to Column E) to sheet 2.

I hope that this helps.

Cheerio,
vcoolio.

vcoolio · Answer 3 · 2017-05-03T06:07:23-0400

Answer 3 / 4

vcoolio

Posts: 1309
Registration date: Thursday July 24, 2014
Status: Moderator
Last seen: July 22, 2021

232
May 3, 2017 at 06:07 AM

Greetings again Blake,

Another method that you may find simpler is a Worksheet_Change event (no buttons required). The code is as follows:-

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub
If Intersect(Target, Columns("A:A")) Is Nothing Then Exit Sub

Application.ScreenUpdating = False

        If Target.Value = "X" Then
        Target.EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
        End If
  
Sheet2.Columns.AutoFit

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

With this code, each time that you place an "X" in Column A and then click away (or press enter or down arrow), the relevant row of data will be transferred to Sheet 2. The "X" must be your last entry per row.

To implement this code:-

- Right click on the Sheet1 tab.
- From the menu that appears, select "view code".
- In the big white field that then appears, paste the above code.

Return to Sheet1 (your input sheet) and test it. But, again, remember to test the code in a copy of your work book first.

Just another option.

Cheerio,
vcoolio.

Report

blakerwilson

Posts: 4
Registration date: Friday April 28, 2017
Status: Member
Last seen: May 3, 2017

May 3, 2017 at 10:29 AM

that might be easier... I am guessing I could replace X with YES or something that would mean something to others using this sheet?

Thanks for your help. The first solution seems to work and I will try the second because I like that idea of eliminating the button.

Report

vcoolio

Posts: 1309
Registration date: Thursday July 24, 2014
Status: Moderator
Last seen: July 22, 2021

232 > blakerwilson

Posts: 4
Registration date: Friday April 28, 2017
Status: Member
Last seen: May 3, 2017

May 4, 2017 at 03:06 AM

No worries Blake. Glad that I was able to help.

Yes, you can replace the "X" with any criteria that you like. Just change it in the codes above.

Cheerio,
vcoolio.

Report

Blocked Profile
May 5, 2017 at 07:56 PM

Once again, vcoolio, so elegant!

Report

vcoolio

Posts: 1309
Registration date: Thursday July 24, 2014
Status: Moderator
Last seen: July 22, 2021

232 > Blocked Profile
May 5, 2017 at 10:01 PM

G'day Mark,

I'm glad that you approve! Hopefully, Blake is no longer in a pickle.

Cheerio,
vcoolio.

Report

blakerwilson
May 10, 2017 at 06:49 PM

I just heard back from the group I am working with and they would like me to modify this code a bit. Instead of a YES, they would like to copy that row to another sheet if there is anything in that cell...

If Target.Value = "YES" Then
Target.EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
End If

is that doable? and if so, what do I need to put in the code to make this work?

Great job on this by the way!

vcoolio · Answer 4 · 2017-05-10T20:28:54-0400

Answer 4 / 4

vcoolio

Posts: 1309
Registration date: Thursday July 24, 2014
Status: Moderator
Last seen: July 22, 2021

232
May 10, 2017 at 08:28 PM

Hello Blake,

Change this line :-

If Target.Value = "YES" Then

to

If Target.Value <> "" Then

This will allow you to place any value in Column A. Make sure that everyone is aware that the criteria needs to be the last entry in any row.

Cheerio,
vcoolio.

Need Help creating an IF THEN scenario [Solved/Closed]

4 replies

Your reply

Subscribe To Our Newsletter!