Retrieving certain fields from a table using date fields as references based on creterias

**McKneezy** · 03-21-2019, 06:21 PM

Hello,

Please see attached a sample workbook, which would involve a complex formula that I am having lots of trouble with.

The goal here is to extract ID's that either changed programs, remained in the same program, or never returned only after any of the 4 withdrawals listed.

You will notice two desired result sections along with a reference table. One desired result is to extract the program in which the ID has withdrawn. The other is to extract the program when the ID returned and the year next to when the program was returned into.

Thank you in advance!

Note: My actual data set and reference table I has many more columns and rows. What I attached is simply a sample.

**Bo_Ry** · 03-21-2019, 11:52 PM

Please try at
C2:E6

Formula:

Please Login or Register  to view this content.

F2:F6

Formula:

Please Login or Register  to view this content.

**McKneezy** · 03-22-2019, 11:56 AM

Hi Bo,

Formulas are not working too well.

Firstly, I notice you do not use the date columns in the reference table. My actual reference table is not in any specific order, as your formula would work assuming the ID are sorted by (earliest to latest date). Can you modify the formula to use the dates as reference fields?

Secondly, the formula is not taking remained in many cases. Note in my data set the columns (and there headings) are not in the same order as the sample work book. I added an ID to the sample workbook to showcase this, however it is not even returning any results. Hopefully you can fix this.

Lastly, I do not know the impact of this, but I am using when you use column($J$4:$J$99) I would use $J:$J in my data set. Would this have any impact?

Thanks for your help once again.

**Bo_Ry** · 03-22-2019, 02:29 PM

Still unclear on some point.

You expect Cup1 or Cup 2 in C7?

12424 2010-2011 Cup1 or Cup2?

12424 2011-2012 Cup1 Withdrawn by self
12424 2010-2011 Cup2 Withdrawn by self

Why does "Stock" is ignored for C6
21086 2016-2017 Shake

21086 2016-2017 Stock Withdrawn by self
21086 2016-2017 Shake Seamless withdrawal

$J:$J is a bad idea, it will take a long time to calculate all 1m+ Data for each cell. adjust to suit your data range.

**McKneezy** · 03-22-2019, 02:40 PM

Hi Bo,

thanks for quick response.

Cup1 and Cup2 are correct. 12424 in year 2010-2011 withdrew from one course then returned in Cup2 (2011-2012). Sorry should have year next to it. My mistake. If I then added a row 12424 (in column A) and 2011-2012 (in column B), the FROM would be Cup2 and the TO would be (did not return).

You are correct, change that ID (cell J25 to another ID for simplification purposes). Again my mistake.

For the ranges I will adjust to my data set range.

In addition two pointers:
- I notice you put "self" in formula, but there is "with" in common in those text fields "withdrawn be self" too. Can't you just use "with". I do not know if this helps.
- If you can use the registration dates instead that would be better, as I do not have any order in my actual reference table.

**Bo_Ry** · 03-22-2019, 03:25 PM

Pelase try at
C2

Formula:

Please Login or Register  to view this content.

F2

Formula:

Please Login or Register  to view this content.

Local has an earlier start date for H2
18390 2016-2017 Enhanced In Progress 2/1/2017
18390 2016-2017 Local Completed 1/7/2016

**McKneezy** · 03-22-2019, 04:29 PM

Hi again Bo,

Thanks for another quick response.

I am getting blanks in many cases using when using column C2 in my data set. The results that I do receive are correct and what I enter the blank results in the sample workbook it retrieves the correct answer and works!? Do you know why this is?

**McKneezy** · 03-22-2019, 04:30 PM

sorry duplicate message.

**Bo_Ry** · 03-22-2019, 10:59 PM

=iferror(…, "" ) gives blank when error, but I don't know what causes an error. I need to see the workbook.

**McKneezy** · 03-23-2019, 09:36 AM

Hi Bo,

I think the blanks is returned because my data set and reference table are over 5400 rows. I know that this was an issue for other formulas and the expert who helped me out with that added ",COLUMNS($CK2:CK2)),1)*1000000,0)),"")" as the end of the formula.

Does this help?

Thanks!

**Bo_Ry** · 03-23-2019, 10:35 AM

I already use *10^6 that cover 999,999 rows.
Have you adapt range from 999 to 5400?

**McKneezy** · 03-23-2019, 12:02 PM

Hi again Bo,

Yes I have and it works now but still receiving incorrect answers.

Please see my next post that provides you a mirror of my data set for rows and columns that are needed.

**McKneezy** · 03-23-2019, 05:34 PM

I have also attached a sample workbook to reflect the same columns and rows in my actual data set and actual reference table. You will notice that many desired results are incorrect.

Thanks Bo!

**Bo_Ry** · 03-24-2019, 07:30 AM

Please try at BW2

=IFERROR(SUBSTITUTE(INDEX('Reference table'!$U:$U,MOD(AGGREGATE(15,6,'Reference table'!$Z$4:$Z$5823*10^6+ROW('Reference table'!$C$4:$C$5823)/('Reference table'!$C$4:$C$5823=$A2)/('Reference table'!$D$4:$D$5823=$B2)/ISNUMBER(SEARCH("Withd",'Reference table'!$O$4:$O$5823)),COLUMNS($BW2:BW2)),10^6)),BV2,"(remained)"),"")

The Index array in Red can use the whole column or start from Row1 $U$1:$U$5823 but the calculating part remains as used range.

**McKneezy** · 03-24-2019, 09:32 AM

Hi Bo,

Ok, the first part of formula now works, however if there are 3 withdrawals, the formula does not return the correct result. I would like it to state (remained) in cell BY2.

Second part of formula is not returning the correct desired results.

Please see updated attachment giving reference to these two measures.

Thanks!

Rob

**Bo_Ry** · 03-24-2019, 09:43 AM

Please try

BW2

Formula:

Please Login or Register  to view this content.

BZ2

Formula:

Please Login or Register  to view this content.

**McKneezy** · 03-24-2019, 10:52 AM

Success Bo!

Your last post is not appearing on this thread, I used the email notification and copied over the formulas. Thank you for all your help much appreciated!

If you can explain (in simple terms) how the formula works that would be great. No worries if you can't

.

Lastly, I want to learn more DAX formulas. Right now I am registered in yet another online program. Is there any program you would suggest I use to learn? Your knowledge of excel seems quite advanced.

Thanks again!

Rob

**Bo_Ry** · 03-24-2019, 11:32 AM

Happy to help.

and thanks for the feedback.

The main part of the formula is Aggregate.
Please check ExcelIsFun for how to it. https://youtu.be/yxun-2epwew
If you still have questions, please let me know.

ExcelIsFun also has many other great Videos to study.

**McKneezy** · 03-26-2019, 01:47 PM

Hello Bo,

Thanks for the tips. I just realized that if an ID has only 1 withdrawal it does not show (remained) if the program did not change.

In this aspect, the formula only works for IDs that have withdrawn more than once (2 or 3 times).

Can you take a look at the attached workbook?

Thanks!

**Bo_Ry** · 03-26-2019, 10:42 PM

Please try at BZ2

Formula:

Please Login or Register  to view this content.

**McKneezy** · 03-28-2019, 11:16 AM

Hi Bo,

Still running into some incorrect results. I have another column that shows a different status "business change" in my data set. I thought copying the formula over would work properly after making the necessary adjustments. However, the formula does not seem to work. I hid some rows to show clearly what the desired result should be. Can you take a look?

Thanks as always.

**Bo_Ry** · 03-28-2019, 11:56 AM

Please see the change in red

CJ42

Formula:

Please Login or Register  to view this content.

**McKneezy** · 03-28-2019, 01:35 PM

Hi Bo,

Two issues:

1
Formula works in sample workbook but does not work in my data set. It is returning the wrong results. Even the year in brackets is wrong

. I copied the formula into row 42 to be sure and it is returning ID programs from the wrong year, but for the correct ID. I copied over the columns again to see if it would work and it does. So I do not know what is going on there.

2
Also it is possible that an ID can Business Change twice. I thought this would be applied to how the withdrawals work, but it does not, as shown in attachment. Attachment is only for issue 2. I have no idea how to resolve issue 1.

Thanks!

**Bo_Ry** · 03-29-2019, 06:26 AM

I'm already lost. Your criteria change a lot from the original question, same formula can't use with this.
Please start a new post, remove all formula, leave manual expected result and explain from the beginning.

**McKneezy** · 03-29-2019, 10:06 AM

Hi Bo,

Apologies. Here you are. It is almost identical to the previous concept. I made the attachment clear.

Hopefully you can figure it out.

**Bo_Ry** · 03-29-2019, 12:22 PM

Please explain the red text.

Do you only need to show years in CL6?

Untitled.png

**McKneezy** · 03-29-2019, 04:23 PM

Hi Bo,

CG 3
ID is doing a "business change" but did not return in another one. This instance should not happen, but my data set is entered in by technicians who may have entered in data incorrectly.

Row 4 is an ID going from plant 2 to plant 5, then from plant 5 to plant 6, then from plant 6 back to plant 5 (with the year ID returned next to it)

Ideally, the year should only show in CJ CK or CL. If CL and CK has result (not blank or (did not return)) then the year should be shown in CK. If CK and CL are blank then CJ would show the year next to the result (not blank or (did nor return).

Lastly, if I were to copy these 6 columns (for example) into columns BS BT BU (formula 1) BV BW BX (formula 2) and make the proper cell references, would this work?

I also notice that I need to sort my reference table to bring back the proper year at times (in my other withdrawal exercise). I think this occurs because sometimes the dates are the same (2010-09-30 to 2012-10-30) for two different years. Can I provide you a sample workbook to showcase this?

**Bo_Ry** · 03-29-2019, 10:47 PM

There is no Plant 6 for ID 28423

Untitled.png

As I mention, Please start a new post, someone else may be able to help you, Don't just rely on me.

Retrieving certain fields from a table using date fields as references based on creterias

Re: Retrieving certain fields from a table using date fields as references based on creter

Re: Retrieving certain fields from a table using date fields as references based on creter

Re: Retrieving certain fields from a table using date fields as references based on creter

Re: Retrieving certain fields from a table using date fields as references based on creter

Re: Retrieving certain fields from a table using date fields as references based on creter

Re: Retrieving certain fields from a table using date fields as references based on creter

Re: Retrieving certain fields from a table using date fields as references based on creter

Re: Retrieving certain fields from a table using date fields as references based on creter

Re: Retrieving certain fields from a table using date fields as references based on creter

Re: Retrieving certain fields from a table using date fields as references based on creter

Re: Retrieving certain fields from a table using date fields as references based on creter

Re: Retrieving certain fields from a table using date fields as references based on creter

Re: Retrieving certain fields from a table using date fields as references based on creter

Re: Retrieving certain fields from a table using date fields as references based on creter

Re: Retrieving certain fields from a table using date fields as references based on creter

Re: Retrieving certain fields from a table using date fields as references based on creter

Re: Retrieving certain fields from a table using date fields as references based on creter

Re: Retrieving certain fields from a table using date fields as references based on creter

Re: Retrieving certain fields from a table using date fields as references based on creter

Re: Retrieving certain fields from a table using date fields as references based on creter

Re: Retrieving certain fields from a table using date fields as references based on creter

Re: Retrieving certain fields from a table using date fields as references based on creter

Re: Retrieving certain fields from a table using date fields as references based on creter

Re: Retrieving certain fields from a table using date fields as references based on creter

Re: Retrieving certain fields from a table using date fields as references based on creter

Re: Retrieving certain fields from a table using date fields as references based on creter

Re: Retrieving certain fields from a table using date fields as references based on creter

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Pivot Table fields based on two counted fields

[SOLVED] Excel SHOULD NOT AUTO-CHANGE formated text fields to DATE FIELDS!

Bookmarks

Bookmarks

Posting Permissions