Home

Two values in one cell

Naomi_5
Occasional Visitor

 

Hi,

 

I am struggling with a formula in a spreadsheet. I am in a library course. We haven't been taught any Excel during class and need to write up an Excel spreadsheet from scratch. The spreadsheet concerns overdue items, library branches etc.

 

I finished this spreadsheet and used this formula to return the item that was most overdue:

=INDEX(C4:C28,MATCH(MAX(F4:F28),F4:F28,0))

 

But our tutor was using last year's spreadsheet data so everything was very overdue. When she realised this she changed the hired-out dates and now there are two items that have the "Maximum Overdue" value. She is on holidays for 3 weeks and this spreadsheet is due in 2 weeks. 

 

A classmate has emailed the tutor, but I wanted to ask if this is possible and see if I could actually find a way of doing it. How would I return 2 text values to a single cell? 

 

Any help is greatly appreciated. 

 

3 Replies

Hello Naomi

 

I set up a quick Power Query solution.

I assumed you only wanted to see the overdue items and not the complete list. In your table every item is an overdue item - even when it is not.

I hardcoded the values for Loan Period and Overdue action period.

I used conditional formatting for the most overdue item(s) and a pivot chart for the distribution of the item types.

 

Highlighted

Hello Naomi, 

In the attached file, I added Overdue Items and Overdue Days in Columns H and I, respectively. The formulas under Column H, starting in H4, return the list of the Overdue Items, which is a concatenation of the Membership Number, Library Name, and Item, through this formula: 

=IF(ROWS(A$4:A4)>COUNTIF(F$4:F$28,">0"),"",LOOKUP(PI(),
1/(F$4:F$28*(COUNTIF(H$3:H3,D$4:D$28&" "&B$4:B$28&" "&C$4:C$28)=0)=MAX(INDEX(
F$4:F$28*(COUNTIF(H$3:H3,D$4:D$28&" "&B$4:B$28&" "&C$4:C$28)=0),0))),
D$4:D$28&" "&B$4:B$28&" "&C$4:C$28))

Meanwhile, the formulas under Column I, starting in I4, return the Overdue Days in Descending Order, through this formula: 

=IF(H4="","",
LOOKUP(PI(),1/(D$4:D$28&" "&B$4:B$28&" "&C$4:C$28=H4),
F$4:F$28))

I know Detlef Lewin prefers the sexy PI() over the standard lookup_value argument of 2. I will make him smile by conceding to his preference.

Cheers!

Twifoo

Twifoo, watch me smile.

74423cbcc2805a7e5cc6e5672628818d

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies