Group data

Bazabelle

New Member
Joined
Oct 6, 2022
Messages
34
Office Version
  1. 365
Platform
  1. Windows
Ok so i have this formula in excel, it works but the issue is that i want obly 1 row, not all the rows to show.

So I want to see if the country Spain in cell A2, exist in sheet Details, to look the data in sheet Details column J and only return "finalized" or "in progress". So if I have 1 cell that has In progress, it returns this, if all cells are with finalized to return finalized. Right now it returns all the rows for this country and returns the value.

=IF('Details'!A2:A1000=A2, IF('Details'!J2:J1000="In progress", "In progress", "Finalized"),"")

So is there a way with a formula to group the data. I guess I coyld do in VBA but really a unexpert in VBa, I understand the language, I am not able to write it.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Will the A2 value always exist on the Details sheet?
If not what should the formula return?
 
Upvote 0
Yes A2 value always exist, irs the countries in both tabs. The first tav that i have the formula should return in 1 line but in details tabd i could have multiple lines for the country.
 
Upvote 0
Ok, how about
Excel Formula:
=IF(COUNTIFS(Details!A2:A1000,A2,Details!J2:J1000,"In progress"),"In progress","Finalized")
 
Upvote 0
Solution
Hi for some reason, I couldn't make @FLUFFs formula work (assuming I read the question correctly) - so I came up with this

Excel Formula:
=LET(arr,UNIQUE(FILTER(Details!J2:J1000,Details!A2:A1000=A1,"")),REDUCE("",arr,LAMBDA(a,b,IF(b="In progress",b,"Finalized"))))

which seemed to work for my own implementation somehow :)

Rob
 
Upvote 0
Ok, how about
Excel Formula:
=IF(COUNTIFS(Details!A2:A1000,A2,Details!J2:J1000,"In progress"),"In progress","Finalized")
This perfectly works for my case, thank you!! I did not know we could use countifs with wording!
 
Upvote 0
I must have misinterrpreted the question .. it’s pretty unusual that @Fluff doesn’t…. 👍
 
Upvote 0
Hi for some reason, I couldn't make @FLUFFs formula work (assuming I read the question correctly) - so I came up with this

Excel Formula:
=LET(arr,UNIQUE(FILTER(Details!J2:J1000,Details!A2:A1000=A1,"")),REDUCE("",arr,LAMBDA(a,b,IF(b="In progress",b,"Finalized"))))

which seemed to work for my own implementation somehow :)

Rob
I just tried this and works as well! Thank you!!
 
Upvote 0
Oh.. ok that’s great, thanks for feedback ..

Cheers
Rob
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top