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
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies