pulling data from cells near unique descriptor

Well, this might get the conversation started so I put together a solution with assumptions. Actually there are 2 solutions I attached. Both solutions require a couple of helper columns (to have the values in Cols C&D repeat downward. I put these in Cols F&G. Cell F2 is this:
=IF(B2="",F1,B2)
And similarly in G2:
=IF(C2="",G1,C2)

The first solutions shows the 4 elements you're wanting based on the Unique numbers in ColB, so in H2 entered:
=FILTER(UNIQUE(B2:B30),UNIQUE(B2:B30)<>0)
Then in I2 (for locus_tag), I entered:
=XLOOKUP($H2&I$1,$F$2:$F$38&$D$2:$D$38,$E$2:$E$38,"")

This formula I copied across and down as needed.

See attached.
(The other solution also in the attached, just assumes the Unique rows are a combination of column B and C. See this solution in columns N:S in the attached.

Originally Posted by Gregb11

For clarity, it's always best to provide the answers that you are expecting (Mock-up the results area of what it should look like when the formulas are applied). What if there is a product, but nothing else. Do you want the product listed an nothing else in that row? The same question regarding locus_tag (or any other the other 3 for that matter)? In other words, does it need to have a locus tag before you consider listing anything else?

***EDIT - added ***

Also, are the categories always listed in the same order? Meaning, is "gene" always before "locus_tag" and is that always before "old_locus_tag", etc. (I realize that they won't all be listed, but as far as those that are listed, will they always be in that order?

Thank you for the reply. Starting off, I believe that every entry has a locus_tag because this genome assembly is based on that identifier. The order for identifiers per unique entry is always in the order shown in the example. If it is easiest to make the formula dependent upon the locus tag then I believe it should work properly.

Originally Posted by Gregb11

Well, this might get the conversation started so I put together a solution with assumptions. Actually there are 2 solutions I attached. Both solutions require a couple of helper columns (to have the values in Cols C&D repeat downward. I put these in Cols F&G. Cell F2 is this:
=IF(B2="",F1,B2)
And similarly in G2:
=IF(C2="",G1,C2)

The first solutions shows the 4 elements you're wanting based on the Unique numbers in ColB, so in H2 entered:
=FILTER(UNIQUE(B2:B30),UNIQUE(B2:B30)<>0)
Then in I2 (for locus_tag), I entered:
=XLOOKUP($H2&I$1,$F$2:$F$38&$D$2:$D$38,$E$2:$E$38,"")

This formula I copied across and down as needed.

See attached.
(The other solution also in the attached, just assumes the Unique rows are a combination of column B and C. See this solution in columns N:S in the attached.

Wow that was quick. Excel code confuses me so I will reserve my questions for until I get this formula applied to the full dataset. Thank you very much and I will update the thread soon.

This did the trick. Thank you so much for solving this for me.