Way to Pull Current Items ?

**chloeyal** · 07-08-2021, 05:54 AM

Hi,
I need a way to find out which items in my database a person currently has. ie. which items have a start date but not an end date. Given that there is potential for the person to have any combination of up to 10 items the possible combinations are too long to code for with the IF and CONCAT formulas that I have previously used. Is it possible to do this another way?
For example by using the test 'if there is a start date but no end date then output the item names in the format "Name 1, Name 2 & Name 3" into column AG'.
Any suggestions would be greatly appreciated, Thanks

**chloeyal** · 07-08-2021, 06:37 AM

Hi, I have added in some more columns indicating whether each of the 10 items is currently possessed by a person, if this makes creating a formula to pull the current items into a single cell any easier?
Thank you.

**Glenn Kennedy** · 07-08-2021, 06:44 AM

I worked off the FIRST attachment. This seems OK to me. Hopefully.... your version of Excel will have TEXTJOIN...

Formula:

Please Login or Register  to view this content.

**Glenn Kennedy** · 07-08-2021, 06:53 AM

What is the difference between this thread and your other thread, started today?? Are they the same???

**chloeyal** · 07-08-2021, 07:11 AM

Hi, thanks for your help, I will try that now.

For the other thread I was trying to find a way to shorten a formula in terms of the number of cell references. In my other thread the end goal was to get the second combination of items whereas this thread the goal was to get the final combination of items. So the requirements for my formulas were different.

**chloeyal** · 07-08-2021, 07:47 AM

Hi,

Thanks again for your help, would it be possible to tweak the formula in order to output the specific names in the columns as opposed to the header "item number"?
I have put some example outputs in the column AG, if this would be possible?

Thank you.

**JeteMc** · 07-10-2021, 07:03 PM

Initially I could see Glen's formula working, however once I selected "Enable Editing" the formula only displayed the first item that met the criteria for a row. I am guessing that is a difference between the subscription version and the 2019 version.
I had to resort to splitting Glen's as follows:
1. Populating columns AI:AR using:

Formula:

Please Login or Register  to view this content.

2. Populating column AH using: =TEXTJOIN(", ",TRUE,AI2:AR2)
Having done that I could then:
3. Populate columns AS:BB using: =IF(AI2="","",INDEX(Table1[@],MATCH(AI2,Table1[#Headers],0)))
4. Populate column AG using: =TEXTJOIN(" & ",TRUE,AS2:BB2)
Let us know if you have any questions.

**chloeyal** · 07-12-2021, 05:44 AM

Thank you for your help, it is really appreciated.

If it is not too much trouble, would you mind helping me create 2 formulas for the columns AG and AH (the second and third combination of items a person owns).
I have entered some manual example outputs to help explain what I am looking for.
Based on the start and end dates of the items, I'd like to create some formulas which will autopopulate the cells based on the names of items a person owns first, second and third.
e.g. they first own Name 1, then lose Name 1 and gain Name 2 and then gain Name 3 in addition to Name 2 (the end date for Name 2 is after the start date of Name 3).

I hope this makes sense.

Thank you again.

**Glenn Kennedy** · 07-12-2021, 05:50 AM

Jete.. I think I've seen this before... hence my "warning". It's rather disturbing that the formula can LOOK like it's working, but deliver the wrong answer... Too many incomparabilities between Excel versions...

**JeteMc** · 07-12-2021, 03:01 PM

Perhaps the following will help display the second and third items.
More helper columns.
The formulas for columns AK and AO are similar to:

Formula:

Please Login or Register  to view this content.

The formulas for columns AL:AN and AP:AR are similar to:

Formula:

Please Login or Register  to view this content.

The formulas for columns AG:AH are similar to: =TEXTJOIN(" & ",1,AL2:AN2)
I imagine that some testing will be needed to insure the output is correct.
Let us know if you have any questions and/or find any issues.
@Glen, have sent you a PM.

**chloeyal** · 07-13-2021, 05:13 AM

Thank you, this is great. Would this work for up to 6 names?
e.g. Formula 2 consisting of 'Name 1, Name 2, Name 3, Name 4, Name 5 & Name 6' or Formula 3 consisting of 'Name 1, Name 3, Name 4, Name 5, Name 6 & Name 7'

Thanks again for all your help!

**chloeyal** · 07-13-2021, 06:36 AM

Also, upon putting this into my database, a NUM error shows (most likely due to the data in my actual database being formatted as short dates in the cells. Is there any way to fix this?
Thank you

**chloeyal** · 07-13-2021, 09:16 AM

Hi,
Upon entering in some different date values to test if the formulas were working I've encountered a NUM error (AK2) that I cannot work out how to solve.
The output for Formula 2 (AG2) should be 'Name 1 & Name 2' because:
Formula 1 (first items owned) is 'Name 1, Name 2 & Name 3'
Name 3 is lost in 2009 (before Name 1 and Name 2)

Is there a way for this to be fixed?
I'm not sure if its an issue to do with this particular output or something more general.
Thanks for your help.

**JeteMc** · 07-13-2021, 01:27 PM

I have made some progress using helper rows (13:22) which are populated using:

Formula:

Please Login or Register  to view this content.

The first set of items are selected using: =IFERROR(INDEX($B13:$AE13,AGGREGATE(15,6,(COLUMN($B13:$AC13)-COLUMN($A13))/((ISNUMBER(SEARCH("item",$B$1:$AC$1)))*($C13:$AD13=0)),COLUMNS($AK2:AK2))),"")
The second set of items are selected using:

Formula:

Please Login or Register  to view this content.

I have not worked out the formula for the third set and will be away from my computer for a while, but will try again if someone has not resolved the issue beforehand.

**JeteMc** · 07-14-2021, 09:32 AM

Here is the formula for the Third Set of Items:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

**chloeyal** · 07-15-2021, 06:11 AM

Hi,
Thank you so much this is perfect!
The only thing I am struggling with is getting the Current Items formulas (BC-BL) to work when I change and input them into my actual database.
I am only getting 0 outputs, rather than 'Item 1' and cannot find the reason why.
Do you be any chance have any ideas as to what could be causing this? Thank you.

**chloeyal** · 07-15-2021, 06:28 AM

Nevermind, I've got it working, thank you so much for all your help!

**JeteMc** · 07-15-2021, 09:24 AM

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

**chloeyal** · 08-06-2021, 09:16 AM

Hello, thanks again for your help with this. I have since continued work on my database and found that additional columns are needed for the 5th, 6th, 7th, 8th, 9th and 10th combinations of items that a person owns, however I am unsure on how to utilise these formulas in order to achieve this. Could you possibly help with this?

Thank you

**JeteMc** · 08-10-2021, 12:01 PM

Here is a different approach.
I have made changes to the arrangement of the data as well as the formulas.
Notice that for the formulas in columns CI:CR a new inequality is added in each successive column.
I.E. in column CJ the formula reads: =IFERROR(INDEX($E44:$CE44,AGGREGATE(15,6,(COLUMN($E44:$CE44)-COLUMN($D44))/($E44:$CE44<>"")/($E44:$CE44<>$CI44),1)),"")
In column CK the formula reads: =IFERROR(INDEX($E44:$CE44,AGGREGATE(15,6,(COLUMN($E44:$CE44)-COLUMN($D44))/($E44:$CE44<>"")/($E44:$CE44<>$CI44)/($E44:$CE44<>$CJ44),1)),"")
Also the formulas in E44:CE53 are array entered formulas which may cause Excel to run slower than normal.
If the set up on Sheet2 looks like something with which you could work, I'll be glad to answer any questions.

**chloeyal** · 08-16-2021, 09:07 AM

Hi, thanks for this, however the layout of the table on sheet 2 is not something that would work within my database as the information is layed out as shown in sheet 1. Is there any way of doing this using the data as it is inputted in sheet 1?

Thank you

**JeteMc** · 08-16-2021, 11:15 PM

I am responding so that you will know that I have seen post #21.
I am not encouraged that there is a way to proceed using the setup on Sheet1 and formulas in posts 14 and 15 to get the 4th through 10th combinations.
Looking at the formulas in cells AK2, AQ2 and AR2 they become increasingly more complex and I believe that they use exponentially more computational capacity which will get worse as we attempt to identify more combinations.
I will focus on finding a way to use formulas and/or Power Query to convert the layout of the data on Sheet1 into the layout on Sheet2 as that seems a more feasible way forward, however it may take a while.

**JeteMc** · 08-19-2021, 11:19 AM

As modeled on sheets 3:4, I feel that I have done as much as I am able using formulas and Power Query to transform the matrix type table into a proper data set and then extract the 10 combinations.
1. On Sheet3 row 13 shows the formulas that were used to produce the column headings for the table in rows 14:24.
2. D15:AE24 are populated using: =IF(LEFT(D$1,3)="end",TEXTJOIN(",",FALSE,B2:D2),"")
3. The range C14:AE24 is converted to a table. Note that column headers are automatically converted from formulas to text.
4. The following Power Query Advanced Editor code is used to produce the table in columns A:D on Sheet4:

Please Login or Register  to view this content.

5. The rest of the analysis is the same as given in post #20 with the exception of the formula for finding the 10 combinations which is:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Way to Pull Current Items ?

Re: Way to Pull Current Items ?

Re: Way to Pull Current Items ?

Re: Way to Pull Current Items ?

Re: Way to Pull Current Items ?

Re: Way to Pull Current Items ?

Re: Way to Pull Current Items ?

Re: Way to Pull Current Items ?

Re: Way to Pull Current Items ?

Re: Way to Pull Current Items ?

Re: Way to Pull Current Items ?

Re: Way to Pull Current Items ?

Re: Way to Pull Current Items ?

Re: Way to Pull Current Items ?

Re: Way to Pull Current Items ?

Re: Way to Pull Current Items ?

Re: Way to Pull Current Items ?

Re: Way to Pull Current Items ?

Re: Way to Pull Current Items ?

Re: Way to Pull Current Items ?

Re: Way to Pull Current Items ?

Re: Way to Pull Current Items ?

Re: Way to Pull Current Items ?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Pull current rows & paste to last row

[SOLVED] Formula to pull current owner

[SOLVED] Need to pull through current month data onto dashboard

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions