Excel Questions

[gallsman]

I have two sheets, A and B.

A contains 20 columns of data and B contains 12, most of which will be a copy of corresponding data from A.

One of the columns in Sheet A is a "Y/N" flag that indicates whether data in the corresponding row should copy across to Sheet B.

Say the first row of data in A is Row 3 and is flagged Y. I want this data to copy across to Row 3 in B.
Then the next row in A with the Y flag is Row 10. I want this data to copy across to Row 4 in B.

Any ideas?

[armaghniac]

I have two sheets, A and B.

A contains 20 columns of data and B contains 12, most of which will be a copy of corresponding data from A.

One of the columns in Sheet A is a "Y/N" flag that indicates whether data in the corresponding row should copy across to Sheet B.

Say the first row of data in A is Row 3 and is flagged Y. I want this data to copy across to Row 3 in B.
Then the next row in A with the Y flag is Row 10. I want this data to copy across to Row 4 in B.

Any ideas?

Do you want to copy this as a once off operation or have a continually updating sheet B?
A once off is just a question of filtering the column with Y/N and copying.

[gallsman]

I have two sheets, A and B.

A contains 20 columns of data and B contains 12, most of which will be a copy of corresponding data from A.

One of the columns in Sheet A is a "Y/N" flag that indicates whether data in the corresponding row should copy across to Sheet B.

Say the first row of data in A is Row 3 and is flagged Y. I want this data to copy across to Row 3 in B.
Then the next row in A with the Y flag is Row 10. I want this data to copy across to Row 4 in B.

Any ideas?

Do you want to copy this as a once off operation or have a continually updating sheet B?
A once off is just a question of filtering the column with Y/N and copying.

Sorry, should have been clear. Sheet A is a live document and will be continuously updated.

[WeeDonns]

You could use 2 new columns in sheet A
Column 1;
=VLOOKUP(A2,SheetB!A:D,3,0)
This looks in sheet B for the corresponding data - where A2 =A2, then return column 3

Column 2:
=IF(C2="Yes",D2,"")
This displays the data returned in the lookup if C2 = Yes otherwise leaves in blank


EDIT: - no need for 2 columns, just put the vlookup inside the if
=IF(C2="Yes",VLOOKUP(A2,SheetB!A:D,3,0),"")

[gallsman]

You could use 2 new columns in sheet A
Column 1;
=VLOOKUP(A2,SheetB!A:D,3,0)
This looks in sheet B for the corresponding data - where A2 =A2, then return column 3

Column 2:
=IF(C2="Yes",D2,"")
This displays the data returned in the lookup if C2 = Yes otherwise leaves in blank


EDIT: - no need for 2 columns, just put the vlookup inside the if
=IF(C2="Yes",VLOOKUP(A2,SheetB!A:D,3,0),"")

Sheet A has the master data and the indicator/flag. Sheet B is the destination and is to only hold copied data from A.

The above would still leave blank rows, no?

What I want is that each successive row that has the Y flag in A copies to the next available row in B. Google sheets has a FILTER() formula for this but Excel doesn't.

[WeeDonns]

Yeah it would leave blank rows

put the formulae in SheetB & add a filter to the column headings to not display blank values
fill the formula down all rows

[gallsman]

Yeah it would leave blank rows

put the formulae in SheetB & add a filter to the column headings to not display blank values
fill the formula down all rows

I need it to be tidier than that unfortunately.

Google Sheets has a FILTER function that does literally the exact thing I'm looking for but Excel only have a beta of it at the minute.

[Maiden1]

Yeah it would leave blank rows

put the formulae in SheetB & add a filter to the column headings to not display blank values
fill the formula down all rows

I need it to be tidier than that unfortunately.

Google Sheets has a FILTER function that does literally the exact thing I'm looking for but Excel only have a beta of it at the minute.

I think below does what you are looking for but it was making my head hurt trying to follow it.

https://www.youtube.com/watch?v=tqCEY5YMyqw

[Maiden1]

Yeah it would leave blank rows

put the formulae in SheetB & add a filter to the column headings to not display blank values
fill the formula down all rows

I need it to be tidier than that unfortunately.

Google Sheets has a FILTER function that does literally the exact thing I'm looking for but Excel only have a beta of it at the minute.

I think below does what you are looking for but it was making my head hurt trying to follow it.

https://www.youtube.com/watch?v=tqCEY5YMyqw

This is an easier to follow article, you can put all matches on a separate tab

https://www.eimagine.com/how-to-return-multiple-match-values-in-excel-using-index-match-or-vlookup/

[macdanger2]

I have two sheets, A and B.

A contains 20 columns of data and B contains 12, most of which will be a copy of corresponding data from A.

One of the columns in Sheet A is a "Y/N" flag that indicates whether data in the corresponding row should copy across to Sheet B.

Say the first row of data in A is Row 3 and is flagged Y. I want this data to copy across to Row 3 in B.
Then the next row in A with the Y flag is Row 10. I want this data to copy across to Row 4 in B.

Any ideas?

Use this formula pasted into cell A2 of sheet 2:

=IFERROR(INDEX(Sheet1!$B$2:$B$1000,SMALL(IF(Sheet1!$C$2:$C$1000=$A$1,ROW(Sheet1!$B$2:$B$1000)-MIN(ROW(Sheet1!$B$2:$B$1000))+1),ROWS($A$2:A2))),"")

And after you paste it into the cell, you need to hit SHIFT+CTRL+Enter - this will create an array and you'll see these brackets {} around the formula in the formula bar (without you having typed them). Any time you edit the formula, you'll need to close the cell using SHIFT+CTRL+Enter or the formula won't work.

In this example, Y (or whatever you're searching for) is placed in cell A1, change this location to wherever you want.
The marker (i.e. Y or N) in the original table is located in Sheet1!$C$2:$C$1000
The value in the original table to be returned is located in Sheet1!$B$2:$B$1000

You can drag it down and it should pull only rows with Ys in the marker columns

Hope that works for you, let me know if it doesn't

[BennyCake]

[gallsman]

Will try that macdanger, thanks.

[andoireabu]

Problem:

In cell F9 I have a date that I would like to change dependent on the value in another cell.  That cell will be in the range I9:JR9 and will contain the number 1.  In row 6 there are dates in sequential order in cells I6:JR6.  So if the number 1 appears in cell M9, I want the value in M6 to appear in cell F9.  If I drag the cell containing the number 1 to cell P9 then I want the date in F9 to update to P6.

Near solution:

=INDEX(I$6:JR$6,MATCH(1,I$9:JR$9,0))

This works if I manually type in the number but not when I drag it left to right.

Any ideas?

[macdanger2]

Problem:

In cell F9 I have a date that I would like to change dependent on the value in another cell.  That cell will be in the range I9:JR9 and will contain the number 1.  In row 6 there are dates in sequential order in cells I6:JR6.  So if the number 1 appears in cell M9, I want the value in M6 to appear in cell F9.  If I drag the cell containing the number 1 to cell P9 then I want the date in F9 to update to P6.

Near solution:

=INDEX(I$6:JR$6,MATCH(1,I$9:JR$9,0))

This works if I manually type in the number but not when I drag it left to right.

Any ideas?

Try removing the $ symbol

[RadioGAAGAA]

Problem:

In cell F9 I have a date that I would like to change dependent on the value in another cell.  That cell will be in the range I9:JR9 and will contain the number 1.  In row 6 there are dates in sequential order in cells I6:JR6.  So if the number 1 appears in cell M9, I want the value in M6 to appear in cell F9.  If I drag the cell containing the number 1 to cell P9 then I want the date in F9 to update to P6.

Near solution:

=INDEX(I$6:JR$6,MATCH(1,I$9:JR$9,0))

This works if I manually type in the number but not when I drag it left to right.

Any ideas?

If I understand what you are trying to do right, your formula worked for me... By drag it left to right I assume you mean there is still only one "1" entry in row 9 rather than copying?

Did you try fully fixing the ranges?

=INDEX($I$6:$JR$6, MATCH(1,$J$9:$JR$9,0))