[SOLVED] return specified columns from corresponding row based on specific conditions

with Power Query

Course code	Course title	Media Fellow	Subject/School	Type	OL - Completed
SK183_2	Nutrition: vitamins and minerals	Julia Cooke	LHCS	Module	Ed

Please Login or Register  to view this content.

Thanks for your help, forgive me but i'm not terribly familiar with power query - where am I pasting this code?

Use this formula in cell P2 of the "STEM archiving decisions" sheet and copy it down to the bottom of your data (or beyond, if you might add some more data - the hyphens help to show where the formula is active):

=IF(AND(N2="Ed",COUNTIF(H2:J2,"Archive")),MAX(P$1:P1)+1,"-")

It identifies those records which meet the criteria and gives each such record a unique sequential identifier. Then in the Archived sheet you can use this formula in cell H2:

=IFERROR(MATCH(ROWS($1:1),'STEM archiving decisions'!$P:$P,0),"")

which identifies the row on the other sheet where the unique sequential numbers occur in column P. You can then use this formula in A2 of the Archived sheet:

=IF($H2="","",INDEX('STEM archiving decisions'!A:A,$H2))

Copy this across to F2 and change the reference in F2 to N:N instead of F:F.

Copy the formulae on row 2 down as far as you need them (until you start to get blanks).

Hope this helps.

Pete

check this one

and I suggest to read this: Learn Power Query

Thank you to sandy666 and Pete_UK for your help! Both solutions worked perfectly.

my pleasure

Glad to help, and thanks for marking the thread as Solved.

You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete