Excel Table with IF & Vlookup function Formula Config

Joined
Jun 4, 2012
Messages
31
Reaction score
0
Points
6
Location
Philippines
Excel Version(s)
2011
Dear Friends,

May I request for assistance please in figuring out the formula for the attached sheet?

The logic I wish to incorporate are as follows;

  • IF B15 is = AA-AE, then product will be equal to $5 to 10 days premium…etc.
  • IF F12 is = USD then F15 F19 selection will be from AA to AE found under "PC Sheet"
  • IF C11=North America then F12= USD)

Hope you can help me configure the attached worksheet, many thanks!

Regards,

Francis
 

Attachments

  • Sample PO Request Form.xlsx
    20 KB · Views: 80
I am not sure if I understood, but try these to see if I did.

In F12:

=MID(TRIM(SUBSTITUTE(VLOOKUP(C11&"*",PC!$G$3:$G$20,1,0),C11,"")),2,3)

in B15:

=IFERROR(TRIM(INDEX(PC!$D$3:$D$114&" "&PC!$E$3:$E$114,MATCH("*"&$F$12&"*",PC!$A$3:$A$114,0)+ROWS($B$15:$B15))),"")

copied down

in D15:

=IF(B15="","",INDEX(PC!$B$3:$B$114,MATCH("*"&$F$12&"*",PC!$A$3:$A$114,0)+ROWS($B$15:$B15)))

copied down
 
See if this is what you had in mind
 

Attachments

  • sample_PO.xlsm
    22.4 KB · Views: 54
After i looked at your instructions again i think i missed something on the first file i uploaded. I think this revised file was more like what you showed in your instructions
 

Attachments

  • sample_PO_Revised.xlsm
    23.7 KB · Views: 45
After i looked at your instructions again i think i missed something on the first file i uploaded. I think this revised file was more like what you showed in your instructions

Hi Tmmy,

I just want to say thank you very much for the help you have extended to me, I was able to combain all that you sent and was able to create a working template.

Veyr best regards,

Francis
 
Sure thing ... glad you were able to find a way to make it work .

Have a good one !
 
Comparing long text file from one excel sheet to another

Greetings from the Philippines!

I am working for a news broadcast network in the Philippines. Currently, we are working on a story on the performance of our lawmakers by looking at the quality of bills they file in Congress.

Our political system allows lawmakers to be reelected on three terms. According to experts, this political set up has allowed this phenomenon: the bills they filed from previous Congress are rehashed in the present Congress. However, empirical data are scarce, or worst non-existent. Experts base their opinion on anecdotal evidence.

It is in this light that we would like to study this phenomenon. We would like to shed light on the magnitude of this issue by being the first network or agency that would embark on an empirical study.

We have retrieved from our Congress’ database the list of bills our lawmakers filed in the 13th and 14th Congress. The bills are formatted in Excel format.

Historically, more than half (of the 200 plus) lawmakers are reelected every Congress, and roughly, they principally author more than 30 bills every Congress. This presents a major obstacle in our study.

In this regard, we would like to seek assistance from you.

We would like to know whether the bills they filed from previous 13th Congress were re-filed in the 14th Congress. We hope that you could help us in knowing what Excel command or function could make our study easier and faster.

Most of the bill titles are very long such as AN ACT STRENGTHENING THE FAMILY AND PRESERVING THE SOLEMNITY OF MARRIAGE CEREMONY REPEALING AND AMENDING CERTAIN PROVISIONS OF EXECUTIVE ORDER NO. 209 OTHERWISE KNOWN AS THE FAMILY CODE OF THE PHILIPPINES AS AMENDED.

That’s why it is hard for us to really do simple Excel IF function or manually look it up from one Excel sheet to another Excel sheet. In the past three decades, we have already 8 Congresses.

And since most of their bills are mere rehashed, sometimes lawmakers do some tweaking by changing the title to make it current. For instance, a lawmaker puts a title “Reproductive Health of 2005.” The bill was archived from the previous Congress. Because he’s reelected in the present Congress, he again refiled the bill and renamed the title “Reproductive Health of 2008.”

We hope that the command is not too strict to account for such variance.

Please do help us as we would like to shed light on this issue. Here is my email: alliage.morales[at]gmail.com

Yours respectfully,

John Alliage Morales
C:%5CUsers%5CSPECIA%7E1%5CAppData%5CLocal%5CTemp%5Cmsohtml1%5C01%5Cclip_image001.gif
Greetings from the Philippines!

I am working for a news broadcast network in the Philippines. Currently, we are working on a story on the performance of our lawmakers by looking at the quality of bills they file in Congress.

Our political system allows lawmakers to be reelected on three terms. According to experts, this political set up has allowed this phenomenon: the bills they filed from previous Congress are rehashed in the present Congress. However, empirical data are scarce, or worst non-existent. Experts base their opinion on anecdotal evidence.

It is in this light that we would like to study this phenomenon. We would like to shed light on the magnitude of this issue by being the first network or agency that would embark on an empirical study.

We have retrieved from our Congress’ database the list of bills our lawmakers filed in the 13th and 14th Congress. The bills are formatted in Excel format.

Historically, more than half (of the 200 plus) lawmakers are reelected every Congress, and roughly, they principally author more than 30 bills every Congress. This presents a major obstacle in our study.

In this regard, we would like to seek assistance from you.

We would like to know whether the bills they filed from previous 13th Congress were re-filed in the 14th Congress. We hope that you could help us in knowing what Excel command or function could make our study easier and faster.

Most of the bill titles are very long such as AN ACT STRENGTHENING THE FAMILY AND PRESERVING THE SOLEMNITY OF MARRIAGE CEREMONY REPEALING AND AMENDING CERTAIN PROVISIONS OF EXECUTIVE ORDER NO. 209 OTHERWISE KNOWN AS THE FAMILY CODE OF THE PHILIPPINES AS AMENDED.

That’s why it is hard for us to really do simple Excel IF function or manually look it up from one Excel sheet to another Excel sheet. In the past three decades, we have already 8 Congresses.

And since most of their bills are mere rehashed, sometimes lawmakers do some tweaking by changing the title to make it current. For instance, a lawmaker puts a title “Reproductive Health of 2005.” The bill was archived from the previous Congress. Because he’s reelected in the present Congress, he again refiled the bill and renamed the title “Reproductive Health of 2008.”

We hope that the command is not too strict to account for such variance.

Please do help us as we would like to shed light on this issue. Here is my email: alliage.morales[at]gmail.com

Yours respectfully,

John Alliage Morales
C:%5CUsers%5CSPECIA%7E1%5CAppData%5CLocal%5CTemp%5Cmsohtml1%5C01%5Cclip_image001.gif
Greetings from the Philippines!

I am working for a news broadcast network in the Philippines. Currently, we are working on a story on the performance of our lawmakers by looking at the quality of bills they file in Congress.

Our political system allows lawmakers to be reelected on three terms. According to experts, this political set up has allowed this phenomenon: the bills they filed from previous Congress are rehashed in the present Congress. However, empirical data are scarce, or worst non-existent. Experts base their opinion on anecdotal evidence.

It is in this light that we would like to study this phenomenon. We would like to shed light on the magnitude of this issue by being the first network or agency that would embark on an empirical study.

We have retrieved from our Congress’ database the list of bills our lawmakers filed in the 13th and 14th Congress. The bills are formatted in Excel format.

Historically, more than half (of the 200 plus) lawmakers are reelected every Congress, and roughly, they principally author more than 30 bills every Congress. This presents a major obstacle in our study.

In this regard, we would like to seek assistance from you.

We would like to know whether the bills they filed from previous 13th Congress were re-filed in the 14th Congress. We hope that you could help us in knowing what Excel command or function could make our study easier and faster.

Most of the bill titles are very long such as AN ACT STRENGTHENING THE FAMILY AND PRESERVING THE SOLEMNITY OF MARRIAGE CEREMONY REPEALING AND AMENDING CERTAIN PROVISIONS OF EXECUTIVE ORDER NO. 209 OTHERWISE KNOWN AS THE FAMILY CODE OF THE PHILIPPINES AS AMENDED.

That’s why it is hard for us to really do simple Excel IF function or manually look it up from one Excel sheet to another Excel sheet. In the past three decades, we have already 8 Congresses.

And since most of their bills are mere rehashed, sometimes lawmakers do some tweaking by changing the title to make it current. For instance, a lawmaker puts a title “Reproductive Health of 2005.” The bill was archived from the previous Congress. Because he’s reelected in the present Congress, he again refiled the bill and renamed the title “Reproductive Health of 2008.”

We hope that the command is not too strict to account for such variance.

Please do help us as we would like to shed light on this issue. Here is my email: alliage.morales[at]gmail.com

Yours respectfully,

John Alliage Morales
C:%5CUsers%5CSPECIA%7E1%5CAppData%5CLocal%5CTemp%5Cmsohtml1%5C01%5Cclip_image001.gif
 
I may not understand for sure what you are requiring, but you might be able to use the Autofilter feature. i have attached a file with some dummy data to show you how it works if you have never used it. If fior example all you can do is say search for all records that contain the word Health or some combination thereof in the Title of the bills than the autofilter might work for you.
 

Attachments

  • Sort_bills.xlsx
    9.6 KB · Views: 28
Hi! Our study would like to track all bills that were re-filed by our lawmakers from the previous congress to the present congress.

Historically, when our lawmakers are re-elected, they just re-file their bills with the exact titles. In order for us to quantify the magnitude or the rate of refiled bills, we would like to look at the exact phrasing or string of titles that were filed in the current congress based on the titles that were recorded in the previous congress.

Suppose our lawmaker filed a bill "Renaming Santiago School into Dawson School" in the last Congress. When this was signed into law and that lawmaker was reelected, he again re-filed the bill with same title. This is what we are tryoing to
 
Suppose our lawmaker filed a bill with a title "Renaming Santiago School into Dawson School" in the last Congress. When this bill was not signed into law and this lawmaker was again re-elected, he would re-filed this bill with the same title. This is where we are tying to track how many of his bills filed in the current congress were rehashed.

In many instances, lawmakers file new bills in the present congress, so it is hard for us to track the bills which are considered refiled since our lawmakers file bills more 30 roughly. From these, sometimes there are 10 bills which are rehashed. Our problem is that more than 100 lawmakers get to be re-elected, historically speaking. And we would to look at at least 8 Congresses with more 100 re-elected lawmakers who author more than 30 bills. So it's quite a daunting task. I would like share our excel files if you like.
 
i would be happy to look at your files(s) to see what i might be able to do. I sent you a PM with my email if you don't want to post them on the forum
 
Hi, Tommy! I can't seem to find your PM. I'm using my phone in replying to you. Can you email me so I can email the dataset? Email me at alliage.morales[at]gmail.com I will wait for your email. Thank you very much!
 
Back
Top