# Extract data from matrix to list according to criteria

1. ## Extract data from matrix to list according to criteria

Hi everyone!

I'm creating a monthly schedule template for scheduling customer diaries as shown below.
Basically, I will insert with an "X" the day that the client has an appointment throughout the month.

1.png

As I insert an "X" for a given customer on a certain date, I would like the customer's name and date to be automatically listed in another spreadsheet as shown below.
2.png

Does anyone have any idea how to come up with this? I tried some combinations with OFFSET, INDEX, MATCH but without success.

2. ## Re: Extract data from matrix to list according to criteria

Plan2

B2=IFERROR(DATE(YEAR(Plan1!\$A\$1),MONTH(Plan1!\$A\$1),INDEX(Plan1!\$B\$2:\$AE\$2,SMALL(IF(Plan1!\$A\$3:\$A\$12=Plan2!\$A2,IF(Plan1!\$B\$3:\$AE\$12="X",COLUMN(Plan1!\$B\$2:\$AE\$2)-COLUMN(Plan1!\$B\$2)+1)),COUNTIF(Plan2!\$A\$2:Plan2!\$A2,Plan2!\$A2)))),"")

Control +shift+enter

copy down

format b2:b19 as date

3. ## Re: Extract data from matrix to list according to criteria

Is it possible to load the customer's name automatically as I mark "X" on a date in my calendar?

Thanks for your help.

4. ## Re: Extract data from matrix to list according to criteria

Plan2

A2=IFERROR(INDEX(Plan1!\$A\$3:\$A\$12,SMALL(IF(Plan1!\$B\$3:\$AE\$12="x",ROW(Plan1!\$A\$3:\$A\$12)-ROW(Plan1!\$A\$3)+1),ROWS(Plan1!\$A\$3:Plan1!A3))),"")

Control+shift+enter

Copy down

5. ## Re: Extract data from matrix to list according to criteria

Hi, I couldn't resist to adapt your CSE functions in regular ones:
Excel 2016 (Windows) 64 bit
A
B
1
Customer
Date
2
=IFERROR(INDEX(Plan1!\$A\$3:\$A\$12,AGGREGATE(15,6,ROW(Plan1!\$A\$3:\$A\$12)-ROW(Plan1!\$A\$3)+1/(Plan1!\$B\$3:\$AE\$12="x"),ROWS(\$A\$2:A2))),"")
=IFERROR(DATE(YEAR(Plan1!\$A\$1),MONTH(Plan1!\$A\$1),INDEX(Plan1!\$B\$2:\$AE\$2,AGGREGATE(15,6,COLUMN(Plan1!\$B\$2:\$AE\$2)-COLUMN(Plan1!\$B\$2)+1/((Plan1!\$A\$3:\$A\$12=\$A2)*(Plan1!\$B\$3:\$AE\$12="x")),COUNTIF(\$A\$2:A2,A2)))),"")
 Sheet: Plan2
Cheers
Erwin

6. ## Re: Extract data from matrix to list according to criteria

I agree .

But not knowing the version of Office

CSE always work

Regards

7. ## Re: Extract data from matrix to list according to criteria

Perfect CARACALLA
Thank you!

8. ## Re: Extract data from matrix to list according to criteria

@hpernaf

If you have office 2010 and later use the formulas

by EastWood

Regards

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1