• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF statement (close but yet so far)

D

dgavilanes

Member
Here is my if statemet, but not quiet right yet


=IF($B4:$B26=$B31:$B86,D4,IF(FALSE,D4="(blank)",NA()))

B4:B26 values go from 1 to 26

B31:B86 values go from 1 to 56


What I'm looking for:

if the value between B4 to B26 is equal to a matching value between B31 and B86 then use the current value in cell D4.If D4 is false then show as an #NA.


Hope it makes sense


Dennis
 
Hi dgavilanes,


As i can see from your previous posts, you have a lot of expectation from IF.. but IF function is not so much smarter like you.. :)

* If will never gives you TRUE for to B4 to B26 (total # of cell 22) and B31 to B86 (total # of cell 55) as 22 is not equal to 55

* So I assume, you want, if any value between B31 to B86, is already mentioned in B4 to B26 then you want to pick the corresponding value from D column... not only D4.. If I am wrong.. stop reading the below, as it is useless..


Code: 
On Error GoTo Posting a Sample Workbook :


* so from D31 write the below formula.

=IF(COUNTIF($B$4:$B$26,B31)>1,VLOOKUP(B31,$B$4:$D$26,3,0),"")

and drag upto B86


* Now what if, a number comes twice in b4 to b26, which value need to put in front of B31 to B86 's corresponding D Column..


Posting a Sample Workbook :

* I will be very helpful for us, IF you can post a sample file..

to post a sample file, go to below URL and read the process, (which is Sticky 2 as previously specified by HUI...)


http://chandoo.org/forums/topic/posting-a-sample-workbook


Regards,

Deb
 
Hi Debraj Roy,


I attached a real sample excel file, I have two worksheets, the HS_Data (data source) and the second worksheet (data) which is a combination )a pivot table and template)

Once is working I will hide the P Table so you can only see the template.


On the Data worksheet, I need to populate the information from the pivot table based onto the template based on selection(Top) , some selections have enough data some do not, select Amundsen HS and you will see all data populated.


The Data ID from the top should match the data Id from the template, this id is for data location, example ID 1 and data populates row data ID 1 and so forth.If a row has no data then the code should include an #NA for that particular row


If you select Alcott HS you will see the current data does not have the same # of rows as the one before. So I need to populate the data only where it corresponds below, any row without data I need to get a N/A. I'm planning to create graphs based on this information.


Let me know if you received this post are able to open it


Regards, Dennis


http://sdrv.ms/OIBgoN
 
Hi Dennis ,


Why don't you use the MATCH function ? Entering a formula such as :


=IF(INDEX(D$4:D$26,MATCH($B31,$B$4:$B$26,0))="(blank)",NA(),INDEX(D$4:D$26,MATCH($B31,$B$4:$B$26,0)))


in D31 , will give you what you are looking for. Copy this across , and downwards as required.


Incidentally , why doesn't your template start from row 60 or so , allowing for all 55 rows in the Pivot Table ? This way , even in the event of the Pivot Table filling up , the template will not be affected.


Narayan
 
Thanks I will get back to work on it!!


Yes, it should start at row 60. This was my sample copy


Do you know anybody that wiil be interested in taking a small project off line?


Regards,


Dennis
 
Hi Dennis ,


I am sure a lot of members of this forum will be interested !


Why don't you post details here , and see how many responses you get ?


Narayan
 
You must log in or register to reply here.
Back
Top