IF with reference to other cells

[theanoon]

Hi all,

I am compiling this table to another worksheet using IFs, but I want the result to not have the empty cell in between.

Name	Type	Type
A	DOG	CAT
B	FISH	PRAWN
A	DOG	CAT
A	DOG	CAT
B	FISH	PRAWN
B	FISH	PRAWN

<tbody>
</tbody>

I'm currently using this =if(IF($A2="A",B2,"")

DOG	CAT
DOG	CAT
DOG	CAT

<tbody>
</tbody>

Is there other workaround that I can compile if A, then DOG-CAT, or if B, then FISH-PRAWN in a proper list?

Thanks in advance

 

[friel300]

Hi theanoon

its not 100% clear what you are trying to do here but I think i get you.

=IF(A2="","",VLOOKUP(A2,$A$2:$C$7,2)&"-"&VLOOKUP(A2,$A$2:$C$7,3))

if its not what you are after then let me know and I'll have another go.

 

[theanoon]

Hi friel300, thanks for your reply.

Yes, perhaps my explanation is not that clear. Im looking to for a table, with the name, minus the empty rows

The current ones are with empty rows:
Name A
Dog-cat
----------*empty row*
Dog-cat
Dog-cat
----------*empty row*
Dog-cat

I'm looking all the rows to be filled up, referring to the whole table e.g.;
Name A
Dog-cat
Dog-cat
Dog-cat
Dog-cat

 

[friel300]

so you need a macro to remove blank rows?

would sorting the data not satisfy your needs?

sorry if im still not 100% getting it.