Collating data from sheets and creating a list

%3CLINGO-SUB%20id%3D%22lingo-sub-1177756%22%20slang%3D%22en-US%22%3ECollating%20data%20from%20sheets%20and%20creating%20a%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1177756%22%20slang%3D%22en-US%22%3E%3CP%3ECan%20anyone%20help%20me%20with%20this%20issue%20please.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20produce%20a%20sheet%20which%20analyses%20a%20table%20of%20data%20for%20a%20particlar%20critera%20and%20then%20produce%20a%20table%20based%20on%20that%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEssentially%2C%20I%20have%20a%20table%20of%202%20columns%20and%201000%20rows.%20Column%20A%20is%20data%20and%20Column%20B%20is%20type.%20For%20the%20sake%20this%20exercise%2C%20imagine%20that%20I%20have%20only%20two%20types%3B%26nbsp%3B%3CSTRONG%3EType%201%26nbsp%3B%3C%2FSTRONG%3Eand%26nbsp%3B%3CSTRONG%3EType%202%3C%2FSTRONG%3E.%20I%20would%20like%20to%20create%20a%20table%20which%20lists%20only%20the%26nbsp%3B%3CSTRONG%3EType%202%3C%2FSTRONG%3E%20entries%20along%20with%20their%20corresponding%20date.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20I%20could%20use%20IF%20to%20look%20at%20the%20data%20and%20check%20for%20the%20types%2C%20but%20this%20will%20then%20give%20me%20a%20table%20with%20an%20empty%20row%20every%20time%20Type%201%20is%20selected.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20suggest%20a%20way%20to%20create%20a%20table%20from%20a%20defined%20data%20set%20that%20will%20return%20entries%20for%20the%20desired%20data%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20this%20makes%20sense.%20I%20am%20confident%20that%20it%20doesn't.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1177756%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20Desktop%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1177789%22%20slang%3D%22en-US%22%3ERe%3A%20Collating%20data%20from%20sheets%20and%20creating%20a%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1177789%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F560809%22%20target%3D%22_blank%22%3E%40joerv007%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20are%20on%20Excel%20with%20dynamic%20arrays%20that%20could%20be%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DFILTER(A2%3AK1000%2CB2%3AB1000%3D%22Type%202%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1189837%22%20slang%3D%22en-US%22%3ERe%3A%20Collating%20data%20from%20sheets%20and%20creating%20a%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1189837%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3EThank%20you%20for%20your%20thoughts%20on%20this.%20Sadly%2C%20dynamic%20arrays%20are%20not%20an%20option%20because%20they%20are%20not%20on%20the%20version%20of%20Excel%20which%20is%20being%20used%20for%20this%20project.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Can anyone help me with this issue please.

 

I would like to produce a sheet which analyses a table of data for a particlar critera and then produce a table based on that data.

 

Essentially, I have a table of 2 columns and 1000 rows. Column A is date and Column B is type. For the sake this exercise, imagine that I have only two types; Type 1 and Type 2. I would like to create a table which lists only the Type 2 entries along with their corresponding date. 

 

I know I could use IF to look at the data and check for the types, but this will then give me a table with an empty row every time Type 1 is selected.

 

Can anyone suggest a way to create a table from a defined data set that will return entries for the desired data?

 

I hope this makes sense. I am confident that it doesn't.

 

Many thanks.

2 Replies
Highlighted

@joerv007 

If you are on Excel with dynamic arrays that could be like

=FILTER(A2:K1000,B2:B1000="Type 2")

 

Highlighted

@Sergei BaklanThank you for your thoughts on this. Sadly, dynamic arrays are not an option because they are not on the version of Excel which is being used for this project.