PDA

View Full Version : Return text in cell based on match of multiple criteria



vanessaaleks
01-21-2009, 02:56 PM
Hi All,
I am including a sample file so you can see what it is that I have in my worksheets. I am currently trying to create a macro to do the following:

1) Find the first blank cell in column N (in my example all cells are blank - but realistically this will not be the case. My existing macro would have already sorted it so the field is filled in for the top rows, and then blank cells are lower on the form) For description purposes I will use row 1 as the first blank cell.

2) In the first blank cell in column N put a formula that will find the matching row in the Cognos sheet, and to fill in Cognos column G from that row, based on matching 3 criteria.
a) Match NWM!J1 by looking in Cognos! column E for the same passenger name.
b) Match NWM!H1 by looking in Cognos! column C for the same total amount.
c) Match NWM!I1 by looking in Cognos! column D for the same transaction date.
3) Fill this formula downward through the endrow based on a count from column A on NWM!.

Note: once this formula is filled in not all entries will match so I am expecting quite a few #N/A to return.

I also plan to lookup columns O:R and U:AK based on this same lookup/match, so
NWM Column O finds the right entry in Cognos column H based on match criteria
NWM Column P finds the right entry in Cognos column I based on match critera
NWM Column Q finds the right entry in Cognos column J based on match critera
NWM Column R finds the right entry in Cognos column K based on match critera
NWM Column U finds the right entry in Cognos column D based on match critera (diviation - all the rest has an equal distance, continues normal alphabetically)
NWM Column V finds the right entry in Cognos column L based on match critera
NWM Column W finds the right entry in Cognos column M based on match critera
NWM Column X finds the right entry in Cognos column N based on match critera
NWM Column Y finds the right entry in Cognos column O based on match critera
NWM Column Z finds the right entry in Cognos column P based on match critera
NWM Column AA finds the right entry in Cognos column Q based on match critera
NWM Column AB finds the right entry in Cognos column R based on match critera
NWM Column AC finds the right entry in Cognos column S based on match critera
NWM Column AD finds the right entry in Cognos column T based on match critera
NWM Column AE finds the right entry in Cognos column U based on match critera
NWM Column AF finds the right entry in Cognos column V based on match critera
NWM Column AG finds the right entry in Cognos column W based on match critera
NWM Column AH finds the right entry in Cognos column X based on match critera
NWM Column AI finds the right entry in Cognos column Y based on match critera
NWM Column AJ finds the right entry in Cognos column Z based on match critera
NWM Column AK finds the right entry in Cognos column AA based on match critera

I really need assistance to get this going, but can probably figure out how to duplicate over and over for the other columns, I just figured best give you more information than less - plus I may learn an easier way of doing things vs. being overly repeditive in my macro.

I am also not opposed to rearranging columns in order to ease the lookup.

Thank you in advance for any assistance you can provide!