Question description:
This user has given permission to use the problem statement for this
blog.
I have a list of data on one sheet, that I want to have copied to another sheet if a particular criteria is met, namely that it matches the client code listed in cell C1. Can you help?
Solved by A. H. in 53 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
16/04/2018 - 12:45
Welcome to GotitPro!
User
16/04/2018 - 12:45
Hi
User
16/04/2018 - 12:45
I see this preview is in Sheets, what I'm trying to do is in excel
User
16/04/2018 - 12:46
I actually got it to work in sheets, but cannot figure it out in excel
User
16/04/2018 - 12:46
the formula is: =QUERY('Harvest Import'!$A$2:$F$435, "select A where (B=C3)")
Excelchat Expert
16/04/2018 - 12:46
Could you please provide me sample data ?
Excelchat Expert
16/04/2018 - 12:47
Function in google sheet and excel desktop are different.
Excelchat Expert
16/04/2018 - 12:47
Query () does not work in excel.
Excelchat Expert
16/04/2018 - 12:49
Please let me know which data you want and on which sheet.
User
16/04/2018 - 12:50
OK, sample data in there. What I'm trying to do is search the data on sheet 1 and copy the data where the info in column B matches the code in Cell C3 on Sheet 3
User
16/04/2018 - 12:50
i guess the Begin and End aren't important for this question, so don't worry about that for now.
User
16/04/2018 - 12:50
I also have a range question as a follow up
Excelchat Expert
16/04/2018 - 12:51
I got your requirement, you want data from sheet one where project code matches, ?right
Excelchat Expert
16/04/2018 - 12:51
right ?
Excelchat Expert
16/04/2018 - 12:52
You want this in excel 2016, right ?
User
16/04/2018 - 12:52
yes
Excelchat Expert
16/04/2018 - 12:53
One more question, as I can see there are more than 1 instances for Code
User
16/04/2018 - 12:53
yes, i want all the lines with that code copied over
Excelchat Expert
16/04/2018 - 12:53
You want to fetch all , right ?
User
16/04/2018 - 12:53
yes
Excelchat Expert
16/04/2018 - 12:54
Thank you for confirmation.
Excelchat Expert
16/04/2018 - 12:54
Please be with me, I am working on it.
User
16/04/2018 - 12:58
Thank you! If you fix this for me, you will be my hero! Then I'll give you a second problem to solve. LOL
Excelchat Expert
16/04/2018 - 12:58
Sure :D
Excelchat Expert
16/04/2018 - 01:05
I am still working.
User
16/04/2018 - 01:06
OK thanks!
Excelchat Expert
16/04/2018 - 01:10
Please check the solution.
Excelchat Expert
16/04/2018 - 01:11
Hi
Excelchat Expert
16/04/2018 - 01:11
Are you there ?
User
16/04/2018 - 01:12
yes
User
16/04/2018 - 01:12
ok that looks right. What's the excel formula?
Excelchat Expert
16/04/2018 - 01:12
=ArrayFormula(INDEX(Sheet1!$A$2:$C$7, SMALL(IF($C$1=Sheet1!$B$2:$B$7, ROW(Sheet1!$B$2:$B$7)-MIN(ROW(Sheet1!$B$2:$B$7))+1, ""), ROW(A1)),COLUMN(A1)))
Excelchat Expert
16/04/2018 - 01:13
This is the formula I have applied, you can see in sheet3
User
16/04/2018 - 01:13
and that will work in excel, not sheets, right?
Excelchat Expert
16/04/2018 - 01:14
Yes
User
16/04/2018 - 01:14
Let me play with it in my actual sheet with the actual names and see if I can make it work. In the meantime, I have question #2
Excelchat Expert
16/04/2018 - 01:15
=INDEX(Sheet1!$A$2:$C$7, SMALL(IF($C$1=Sheet1!$B$2:$B$7, ROW(Sheet1!$B$2:$B$7)-MIN(ROW(Sheet1!$B$2:$B$7))+1, ""), ROW(A1)),COLUMN(A1))
Excelchat Expert
16/04/2018 - 01:15
Use this in excel and press Ctrl+shift+Enter to convert the same into array formula.
User
16/04/2018 - 01:16
If I have several invoices listed on Sheet 3 and i want to copy them to the invoices sheet, I know how to do that... but how do I copy the invoices from all the sheets between begin and end
User
16/04/2018 - 01:16
so use what you typed into chat just now?
Excelchat Expert
16/04/2018 - 01:18
Only one question per session is permitted as per company policy
Excelchat Expert
16/04/2018 - 01:18
Please repost for other query.
User
16/04/2018 - 01:19
OK, can you hang on while I try to get this to work in Excel?
Excelchat Expert
16/04/2018 - 01:20
Sure.
User
16/04/2018 - 01:23
I'm getting a ?Name# error
Excelchat Expert
16/04/2018 - 01:25
Please check the references
Excelchat Expert
16/04/2018 - 01:26
Also press Ctrl+shift+ enter in formula baar
Excelchat Expert
16/04/2018 - 01:27
Are you there ?
Excelchat Expert
16/04/2018 - 01:28
See the sheet3 for example where I have applied the same formula. You columns should be same as in the sheet visible on right pane.
User
16/04/2018 - 01:29
I got it to work, but it's only giving me one row of data, not all the instances
User
16/04/2018 - 01:29
Oh, I have to drag the formula down...
Excelchat Expert
16/04/2018 - 01:29
You will have drag down
Excelchat Expert
16/04/2018 - 01:29
Yes
User
16/04/2018 - 01:30
I dragged down obviously more rows that I have data for and it gives me a #NUM error, any way to make that go away or no?
User
16/04/2018 - 01:30
I'm happy, just curious about that?
Excelchat Expert
16/04/2018 - 01:30
use iferror( formula, ")
Excelchat Expert
16/04/2018 - 01:31
=iferror(INDEX(Sheet1!$A$2:$C$7, SMALL(IF($C$1=Sheet1!$B$2:$B$7, ROW(Sheet1!$B$2:$B$7)-MIN(ROW(Sheet1!$B$2:$B$7))+1, ""), ROW(A1)),COLUMN(A1)),"")
User
16/04/2018 - 01:32
Am I just adding that to the front of the other formula?
Excelchat Expert
16/04/2018 - 01:32
Yes, actually formula within iferror
Excelchat Expert
16/04/2018 - 01:32
As I did above.
User
16/04/2018 - 01:33
Is there a way to unarray it so i can add it to the formula i already modified
Excelchat Expert
16/04/2018 - 01:33
IFERROR( formula, "")
Excelchat Expert
16/04/2018 - 01:33
Can you paste your modified formula here ?
User
16/04/2018 - 01:34
=INDEX(Harvest!$A$2:$I$700, SMALL(IF($D$1=Harvest!$C$2:$C$700, ROW(Harvest!$C$2:$C$700)-MIN(ROW(Harvest!$C$2:$C$700))+1, ""), ROW(A1)),COLUMN(A1))
User
16/04/2018 - 01:34
Oh, never mind...
User
16/04/2018 - 01:34
When I clicked in the formula bar it took the array off
User
16/04/2018 - 01:34
ha
Excelchat Expert
16/04/2018 - 01:34
=IFERROR(INDEX(Harvest!$A$2:$I$700, SMALL(IF($D$1=Harvest!$C$2:$C$700, ROW(Harvest!$C$2:$C$700)-MIN(ROW(Harvest!$C$2:$C$700))+1, ""), ROW(A1)),COLUMN(A1)),"")
Excelchat Expert
16/04/2018 - 01:35
Yeah, it goes on click
User
16/04/2018 - 01:36
Beautiful...
User
16/04/2018 - 01:36
So, what is an array? I've never used that before
Excelchat Expert
16/04/2018 - 01:37
It work on range instead of single cell
Excelchat Expert
16/04/2018 - 01:37
Hope you are satisfied with the solution provided.
User
16/04/2018 - 01:38
Oh, cool.
User
16/04/2018 - 01:38
Yes, thank you!
User
16/04/2018 - 01:38
My hero!
Excelchat Expert
16/04/2018 - 01:38
Please leave your valuable feedback.
User
16/04/2018 - 01:38
Will do! Good night!
Excelchat Expert
16/04/2018 - 01:38
Thank you for choosing gotitPro!
Excelchat Expert
16/04/2018 - 01:38
Good night!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
