Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

If TL;DR - Just go down to the EDIT...

I have a spreadsheet with a table - the table is a comparison of two different (but similar) sets of data. So basically 3 columns with the 3rd being the result column with the formula:

=IF(ISBLANK([@[column_name]]),"",IF(COUNTIF([column_name2],[@[column_name]])>0,TRUE,FALSE))

I would like a 4th column that would hold the contents of [@[column_name]] if the result column [@[column_result]] is TRUE, but if the result is FALSE, default it to blank, but some how allow the end user a way that they can populate it with [@[column_name]] if they want. Or of course they could always manually enter something.

This spreadsheet is going to be used repeatedly, it's more of a template, so the data and row counts will change every time - that's why it's all in a table.

I've thought about maybe having a dynamically generated drop down list that defaults to colmn_name if TRUE and blank if FALSE, but I'm not sure how to do that. Or maybe a button in the cell if Result is false, that when clicked, disappears and populates the cell with column_name...

Additional Info: The spreadsheet is basically comparing the fields from two SQL tables and highlighting the differences (there are far more than 3-4 columns, but for the sake of this question, I dumbed it down). Eventually the data from tableA needs to be mapped to tableB, so I'm using excel to write and format the bulk of the SQL. There are many tables and many fields that need to be compared.

Also, sorry if this question sucks... I know it does, but I don't really know how to best ask it.


EDIT:

Below is a picture of my spreadsheet. Column M will = column A if columns E-H are true in other words: IF(colsE-H == TRUE) ColM = ColA; ELSE ColM = ""; If there isn't a value in M then the user needs to do some manual investigating, if everything checks out (usually does), then the user will copy/paste A into M. I would like to have like a dropdown or a button or something that would allow Col M to be populated with Col A's value at the click (two clicks?) of a mouse.

screenshot of my spreadsheet

EDIT2: That pic got shrunk a lot. Here is a link: Screenshot

share|improve this question
    
I’m somewhat experienced in Excel, but I’m not familiar with your use of square brackets and at signs. I spent more than five minutes Googling it, with no useful result, and the Excel help itself was totally useless. Could you please give a hint as to what you’re doing with them –– maybe a keyword or two that will produce meaningful search results? –  Scott Apr 9 '13 at 22:35
    
Scott, search Excel table, structured references. Try starting here office.microsoft.com/en-us/excel-help/… –  dav Apr 10 '13 at 12:36
    
Jreed121, is there a reason you're doing the manipulation in Excel and not in SQL? I think it would be substantially easier to keep it in SQL and not insert Excel in the middle. –  dav Apr 10 '13 at 12:39
    
@pnuts - I'll se what I can do, but simplifying it for a figure wont be easy. –  jreed121 Apr 12 '13 at 18:29
    
@DavidVandenbos, I could do this at the SQL level, but there is so much logic that is at play in the spreadsheet. Plus conditional formatting and all that helps as this all boils down to a human making the decision in the end. Plus, I've already made the spreadsheet (minus what I'm asking in my question) and I'd rather not start from scratch. This really is a "nice-to-have" because it's not to difficult to copy/past the text. –  jreed121 Apr 12 '13 at 18:33

1 Answer 1

Even with the image (incidentally because of the great quality, I found that more useful than the Screenshot) I am still not entirely clear. It may be that you are after an outer join (not something Excel provides). Also not clear why your last three rows are not in red, nor the significance of your yellow highlighting.

However, it looks as though it would be helpful to identify where column_name does not feature in the list for column_name2.

A VLOOKUP would serve for that:

=VLOOKUP(A18,I:I,1,FALSE)

This will return an error if it does not.

If it does exist, then do field_type, max_length and nullable all match? Easiest to do this ‘in bulk’ but can be split into four TRUE/FALSE columns (as you have). In bulk an extra column would be helpful (say P). In P18 copied down as required:

=I18&","&J18&","&K18&","&L18

In ColumnOeach NGPROD column_name, field_type, max_length and nullable can then be searched for in ColumnF, with “Col doesn't exist”, where that is so:

=IF(ISERROR(VLOOKUP(A18,I:I,1,FALSE)),"Col doesn't exist",VLOOKUP(A18&","&B18&","&C18&","&D18,P18:P34,1,FALSE))

Results in ColumnO should be one of:

  1. The four fields that match (comma separated) [I’d prefer just “blank” where all match but am trying to keep to the spirit of the OP]
  2. Col doesn’t exist [as explained above]
  3. #N/A – ie column_name exists as a column_name2 but one or more of field_type1 / max len1 and nullable1 does not match with field_type2 / max_len2 / nullable2 respectively.

From the above, because the reference is the NGPROD database, ColumM can be populated as below in M18 and copied down as required:

=IF(ISNA(O18),"",IF(O18="Col doesn't exist","",A18))

Then M18:M34 can be validated against a list of A18:A35.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.