extract data from multule rows into one row based on certain criteria

[bohon79]

Good Morning,

Been trying to find a solution to a formula problem but unable to find it and hopefully some can help. Found this forumla online that works great for what i want it to do but for some reason puts a date (1/1/1900) in when the cell its referencing is blank with no date in it. Here is the formula:

=TOROW(FILTER($B$2:$G$496,$A$2:$A$496=A2))

What i am trying to do is take data to consolidate onto on row as is below based on data that is equal. Column A and b will always be constant but c,d,e, will change.:

and convert to

but as you can see it puts a date in when there is no date. Any help in fine tuning the formula to make sure if a date is blank and show blank would be helpful. Also if there is a way for column b to only show up once rather than everytime that would be great as well.

thank you.

 

[Fluff]

How about

=LET(a,TOROW(FILTER($B$2:$G$496,$A$2:$A$496=A2)),IF(a="","",a))

 

[bohon79]

Seems to be working, thank you.

 

[Fluff]

You're welcome & thanks for the feedback

 

[bohon79]

question, does this not work over multiple sheets. it works when i use it on the same sheet but when i try to use the formula on a seperate sheet that links to the data on another sheet it comes back with a value error. tried multiple options that usually works for me but nothing.

 

[Fluff]

What did you try?

 

[bohon79]

I think i figured it out while playing around. here is what i used for it to work the way i wanted it to. I took the formula that i post originally and some of yours and it worked. here is what it came out to:

=LET(a,TOROW(FILTER('HCD - Awards and Recognitio...'!$B$2:$G$496,'HCD - Awards and Recognitio...'!$A$2:$A$496=C1)),IF(a="","",a))

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[bohon79]

thanks again for all your help. i know certain ways to filter but the more complex is were i get lost. I have one more question. is there a way to add and additional filter to the following formula that looks in column b for a certain text between the hyphens

=UNIQUE(FILTER('HCD - Awards and Recognitio...'!$A$2:$A$4989,'HCD - Awards and Recognitio...'!$A$2:$A$4989<>""))

so if i only want to filter column a when the criteria meets a certain text. is that posible. hopefully i explained it correctly

 

[Fluff]

Like

=UNIQUE(FILTER('HCD - Awards and Recognitio...'!$A$2:$A$4989,('HCD - Awards and Recognitio...'!$A$2:$A$4989<>"")*(isnumber(search("-mil-",'HCD - Awards and Recognitio...'!$B$2:$B$4989)))))