fill reference table based on data available in master Table

**jsimha** · 01-31-2013, 04:40 AM

Hi,

I am trying to fill the value Y in reference table rows where it matches to data.
when i try if combine with vlookup it doesnt work coz reference table check value present in multiple times in master data of course row content is different.

See the sample excel worksheet attached, please advice simple that can be used to achieve the task.

Regards,
jsimha

**:) Sixthsense :)** · 01-31-2013, 04:48 AM

In D4 cell of Reference Table-Sheet

Formula:

Please Login or Register  to view this content.

Drag it down and right.....

**OllieB** · 01-31-2013, 04:52 AM

Hi Jay,

*** CORRECTED VERSION ***

The following formula can be used in column B

Please Login or Register  to view this content.

**jsimha** · 01-31-2013, 06:00 AM

Thanks Olaf and Sixth sense.

To : Sixsense:
Given formula
=IF(COUNTIFS('Master Data'!$A$2:$A$11,D$1,'Master Data'!$B$2:$B$11,$A4),"Y","")

Changed to :
=IF(COUNTIFS('Master Data'!$A$2:$A$5484,EB$2,'Master Data'!$B$2:$B$5484,$EB5),"Y","")

Result : it shows 0 value.

To:
I used formula =IF(ISNA(MATCH(A4,'Master Data'!B:B,0)),"N","Y") but it doesn't work.

Regards,
Jay

**:) Sixthsense :)** · 01-31-2013, 06:05 AM

Does the below one shown Result : 0 value ha ha ha NO WAY..........

=IF(COUNTIFS('Master Data'!$A$2:$A$5484,EB$2,'Master Data'!$B$2:$B$5484,$EB5),"Y","")

**OllieB** · 01-31-2013, 06:11 AM

Hi Jay,

The MATCH formula returns the row number where the argument (coilumn A) is found in column B on worksheet 'Master Data' or #N/A when not found. The IF formula converts that result in to Y or N. Should work without a problem, it does in the version I have here

**jsimha** · 01-31-2013, 06:21 AM

Hi,

see attached excel, worksheet: OLAF-1.

result should be same as in reference data.

Regards,
Jay

**OllieB** · 01-31-2013, 06:38 AM

Ok, from your requirement I deducted that you only needed to look at column A. I did not understand that you want to look for column A in combination with RNAME1, RNAME2 and RNAME3 and show Yes/No for the existence of the combinations of values. (I was looking to populate column B, silly me).

**OllieB** · 01-31-2013, 06:43 AM

Using the originally formula posted by SixthSense in post #2, I managed to get the result you were looking for (I think)

Please Login or Register  to view this content.

**jsimha** · 01-31-2013, 11:38 AM

Dear Olaf,

Thanks for explaining and amending it to suit requirement.
Great help, completed the job in minutes which would take 10 days if i do it manually

Regards,
Jay

**OllieB** · 01-31-2013, 12:19 PM

Please remember to click on the star icon if you are happy with my contribution

fill reference table based on data available in master Table

LinkBack

Thread Tools

Rate This Thread

Display

fill reference table based on data available in master Table

Re: fill reference table based on data available in master Table

Re: fill reference table based on data available in master Table

Re: fill reference table based on data available in master Table

Re: fill reference table based on data available in master Table

Re: fill reference table based on data available in master Table

Re: fill reference table based on data available in master Table

Re: fill reference table based on data available in master Table

Re: fill reference table based on data available in master Table

Re: fill reference table based on data available in master Table

Re: fill reference table based on data available in master Table

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions