Forum Discussion
Lookup against multiple columns and return header values
You need, I think, to give a bit more information about how the data are arranged here:
- what's the underlying connection between the data under the yellow headings and the data in columns F and G?
- Are rows F5:G14 all referring to a single employee or single office?
- You have nine columns in yellow, but ten rows for each combination in columns F and G...so IF I follow what you're asking in terms of transposing the headings with "Yes" to a corresponding --but how is it corresponding?!-- row in column I, why the extra row?
- You have removed--for sake of confidentiality--the identifiers in columns A-C, but it might well be that what you're wanting to do would be greatly facilitated by the presence of some (false) names and IDs. Could you, therefore, put together a greatly reduced but more complete spreadsheet, more representative of the kind of data you're actually dealing with.
At the very least, though, if you expect to use some logic in doing this, you'll need to explain things a lot more completely in terms of the relationships between the various components--individual columns and rows--as well as whole contiguous areas. Otherwise you're in effect blind-folding the people from whom you are requesting help.
- the connection is that these are desk feature corresponding to a single desk but whilst they presented here horizontally i need presented vertically so need the headers populated in column I where there is a "yes" found against columns J to R.
- F5:G14 all refer to a single desk
- I have corrected the issue with the extra row per desk feature in column G
You still have a long way to go to fully explain the logic behind the data.
For example:
- do cells J5:R5 all refer to the single employee represented by Floor 42 and Space E01? or
- does each row in J5:R13 refer to the corresponding single row in the F5:G13? or
- something else altogether?
The examples you added in column I confirmed what was already pretty clear. But you still haven't explained how one can get there from the data. There is so much redundancy in the rows with the yellow column headings that I can't make sense of them. A good database design would NOT have repeating rows for the same individual/office. But it certainly looks as if that's what yours does.
In other words, you already have the data for individual 42/E01 in the first row. It's laid out horizontally; why does it need to be vertical? What are you doing that can't be accomplished by making this spreadsheet 1/9th the size? Eliminate the redundancies.