Question description:
This user has given permission to use the problem statement for this
blog.
Hello - I have a giant spreadsheet that I regularly need to go into to find just one row, copy that data, and paste it into another small spreadsheet which I use to do the work I need to do with the data. Is there a way I could do so without having to open the giant spreadsheet and find the right row every time? I can show you the spreadsheets and the process if that helps.
Solved by E. Y. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
16/10/2018 - 03:15
Welcome to excelchat!
Excelchat Expert
16/10/2018 - 03:15
Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.
User
16/10/2018 - 03:15
Sure - is there a way to pay to use you more to handle additional questions/problems?
Excelchat Expert
16/10/2018 - 03:16
Unfortunately as it is, you can buy unlimited number of sessions but you can only get help on only one specific problem during a session.
User
16/10/2018 - 03:17
Okay that's fine - I've got a number of things I'd like assistance with so if those goes well I'm eager to do more sessions!
Excelchat Expert
16/10/2018 - 03:17
Great!
Excelchat Expert
16/10/2018 - 03:18
So, about the problem that was described in the description, what do you have in the file and how do you determine which row you need?
Excelchat Expert
16/10/2018 - 03:18
Please also send me the file in question.
User
16/10/2018 - 03:19
So here's the deal - I run a company that helps people fill out paperwork for their boats. I have a HUGE database with information on every boat registered in the US. I'll copy a link here because it's a big file: https://www.dropbox.com/s/r6xfdbpoc8icrxw/Merchant%20Vessels%20Torqued.xlsx?dl=0
User
16/10/2018 - 03:20
I have a second much smaller spreadsheet which I use just for my customers: https://www.dropbox.com/s/e2m332q6klxgds8/The%20Master%20Excel.xlsx?dl=0
User
16/10/2018 - 03:21
So when I get a customer, I look up the customer's boat in the huge spreadsheet, and then copy and paste the customer's boat information from the huge spreadsheet with ALL the boats in the US, into the second spreadsheet, which I then use to draft boat paperwork for my customers.
Excelchat Expert
16/10/2018 - 03:21
I'm downloading the files.
User
16/10/2018 - 03:22
I'm looking for a way that I can streamline the process without having to open up the huge spreadsheet and copy and paste every time. Like, if I could simply put in the customer's name in the small spreadsheet, and then it would automatically pull the relevant data from the huge spreadsheet. Is anything like that possible?
Excelchat Expert
16/10/2018 - 03:22
Meanwhile, how do you lookup a customers boat? With name or customer number or something else?
Excelchat Expert
16/10/2018 - 03:22
Yeah, something like that can be done.
User
16/10/2018 - 03:23
Usually with the customer number which is called "Official Number" in the spreadsheets - that number is unique to each boat so it's the best method. I'd love it if I could simply type in the boat number in the small spreadsheet and then all the appropriate data would magically appear.
Excelchat Expert
16/10/2018 - 03:24
That is very much possible. Please stay with me.
Excelchat Expert
16/10/2018 - 03:25
Which column is official number in the spreadsheets?
User
16/10/2018 - 03:26
It's column K in both spreadsheets
Excelchat Expert
16/10/2018 - 03:29
Alright, I have both files opened in my pc.
User
16/10/2018 - 03:29
Also if we run out of time can I pay to continue?
Excelchat Expert
16/10/2018 - 03:30
You need to copy the whole row from the master file to the 2nd file by matching column K value which you would manually input?
Excelchat Expert
16/10/2018 - 03:30
We can extend for another 20 minutes for free.
User
16/10/2018 - 03:30
Yup - so currently I just highlight the entire row and copy it over from the master file to the second file.
User
16/10/2018 - 03:31
But the solution I'd like would be one where I simply type in the boat number in column K of the smaller file, and it pulls the matching row from the huge file without having to open the huge file.
Excelchat Expert
16/10/2018 - 03:31
You will be asked shortly to extend session. Please select YES.
User
16/10/2018 - 03:32
Thanks - just did so.
Excelchat Expert
16/10/2018 - 03:35
Can you tell me how many rows of data are there in the big file?
User
16/10/2018 - 03:35
368,120
Excelchat Expert
16/10/2018 - 03:36
Thank you. Please stay with me.
User
16/10/2018 - 03:38
Roger, much appreciated!
Excelchat Expert
16/10/2018 - 03:38
The files size is proving to be overwhelming for my pc's processing power, thus taking a bit time. Please stay with me.
User
16/10/2018 - 03:39
Yeah, that's exactly why I want to be able to do this without opening up the spreadsheet every time. What if I sent you just the first 100 rows of the spreadsheet in a smaller file, would that make it easier?
Excelchat Expert
16/10/2018 - 03:40
Just to make sure, even if you don't want to open the 2nd file where you are looking for information, it will take the same amount of time for the formula to return a result.
Excelchat Expert
16/10/2018 - 03:40
I have the formula.
Excelchat Expert
16/10/2018 - 03:41
I have wrote a index/match formula.
User
16/10/2018 - 03:41
Okay, gotcha. Thank you!
Excelchat Expert
16/10/2018 - 03:41
Please put the two files in the same folder on your pc.
User
16/10/2018 - 03:42
Okay great, that's already done.
Excelchat Expert
16/10/2018 - 03:42
This formula requires that you keep the files in the same folder.
Excelchat Expert
16/10/2018 - 03:42
=INDEX('[Merchant Vessels Torqued.xlsx]Merchant Vessels of the United '!A$2:A$368120,MATCH($K2,'[Merchant Vessels Torqued.xlsx]Merchant Vessels of the United '!$K$2:$K$368120,0))
Excelchat Expert
16/10/2018 - 03:43
Now this formula you give you the information of column A from then 2nd file for a official number that is being inputted in the cell K2
Excelchat Expert
16/10/2018 - 03:44
You can test it by placing the formula in A2 and input a official number in K2
User
16/10/2018 - 03:45
Okay great, thank you! Does this only work if the two spreadsheets have identical columns, like in this instance?
User
16/10/2018 - 03:45
Or is there a way to make it work if I have to transpose columns - like if the data in column K of one spreadsheet is in column L in another spreadsheet?
Excelchat Expert
16/10/2018 - 03:45
What do you mean again?
Excelchat Expert
16/10/2018 - 03:46
Oh I see.
Excelchat Expert
16/10/2018 - 03:47
You mean if the file where you need data contains official number in column K but the main database contains the official number in column L, then what?
User
16/10/2018 - 03:47
Exactly.
Excelchat Expert
16/10/2018 - 03:47
See the MATCH($K20,'[Merchant Vessels Torqued.xlsx]Merchant Vessels of the United '!$K$2:$K$368120,0) part of the formula?
Excelchat Expert
16/10/2018 - 03:48
here it means, we are matching the value of K20 with column K of Merchant Vessels Torqued.xlsx workbooks Merchant Vessels of the United worksheets column K.
Excelchat Expert
16/10/2018 - 03:49
This returns a row number, and then =INDEX('[Merchant Vessels Torqued.xlsx]Merchant Vessels of the United '!A$2:A$368120 part of the formula returns a value from the same row numbers column A of Merchant Vessels Torqued.xlsx workbooks Merchant Vessels of the United worksheets
Excelchat Expert
16/10/2018 - 03:49
Making sense?
User
16/10/2018 - 03:50
It does, thank you! So I just pasted the formula in A2 of my working spreadsheet and a window opened for me to select a file. Should I select the huge spreadsheet?
Excelchat Expert
16/10/2018 - 03:50
So, if we were to match against column L, then we would have wrote MATCH($K20,'[Merchant Vessels Torqued.xlsx]Merchant Vessels of the United '!$L$2:$L$368120,0)
Excelchat Expert
16/10/2018 - 03:51
You have to open the two files for the first time only. So, first open both files and then paste the formula in A2. After than you won't have to keep the huge file opened.
Excelchat Expert
16/10/2018 - 03:53
Once you paste the formula in A2, after than you can get other columns data just by dragging cell A2 across the row. (except for column K, there should already be a value, the official number).
User
16/10/2018 - 03:53
Okay - stand by, unfortunately I'm not able to have it work so far
Excelchat Expert
16/10/2018 - 03:54
I'm here.
Excelchat Expert
16/10/2018 - 03:55
If you are still being asked to select a file, please make sure that the file names on your pc is the same as the file you sent to me.
User
16/10/2018 - 03:55
Thanks - it looks like the name of the file in your formula is not the same as the current name for the file, so let me remedy that and see if it works.
Excelchat Expert
16/10/2018 - 03:55
Hmm. That is what I was suspecting.
User
16/10/2018 - 03:56
Allright, that's remedied, let's see how it goes.
User
16/10/2018 - 03:58
Allright, looks like we've got functionality, thank you! And this process, it's called index and matching?
Excelchat Expert
16/10/2018 - 03:58
The formula is named index/match, the process in a lookup process.
Excelchat Expert
16/10/2018 - 03:59
You got the desired result, right?
User
16/10/2018 - 03:59
I did. I will probably need another session to deal with another instance where I'm changing from two spreadsheets where all the columns are wildly different, so look forward to using you guys again soon!
Excelchat Expert
16/10/2018 - 04:00
If you need any clarification, you can ask me.
Excelchat Expert
16/10/2018 - 04:00
We have still 14 minutes left.
User
16/10/2018 - 04:02
Okay, thanks. So I want to do this with another file, which isn't so big: https://www.dropbox.com/s/87e49lmy4rt229v/full%20archive%20of%20form%202365494%20on%202018-10-16.xlsx?dl=0 . The issue is that the columns don't match up at all, is there a way to make this work: https://www.dropbox.com/s/87e49lmy4rt229v/full%20archive%20of%20form%202365494%20on%202018-10-16.xlsx?dl=0
Excelchat Expert
16/10/2018 - 04:03
With index/match the things we need to know is, what is the common between the two files/sheets/tables.
Excelchat Expert
16/10/2018 - 04:03
In your case, then common thing is official number, right?
User
16/10/2018 - 04:05
Exactly - the official number is the common thing, but in this case the columns are all over the place, so for example email is in column D in the one spreadsheet, and column E in the other.
Excelchat Expert
16/10/2018 - 04:05
That is not a problem at all.
Excelchat Expert
16/10/2018 - 04:07
The syntax of index/match is =INDEX(reference,MATCH(lookup_value,lookup,range,type))
Excelchat Expert
16/10/2018 - 04:08
Let's take the example you made, so, reference is what you want as the result, lookup-value is official number and lookup range is the column where this official number can be found in 2nd file.
User
16/10/2018 - 04:08
Ahh. So I can just do one for each individual cell in the row and then I'll have a complete set where everything transposes correctly. Thank you, I get it!
Excelchat Expert
16/10/2018 - 04:09
So, to make sure, if in the file where we are wring formula, email is in column D, we write the formula in column D but in the formula make the reference to column E of 2nd file.
Excelchat Expert
16/10/2018 - 04:09
Making sense? The formula will give us the result wherever we are placing it.
User
16/10/2018 - 04:09
Yup. I get it, because we're just doing one cell at a time. Awesome, this was fantastic and super helpful!
Excelchat Expert
16/10/2018 - 04:09
I'm glad to be able to help.
Excelchat Expert
16/10/2018 - 04:10
Anything else?
User
16/10/2018 - 04:10
Nope, that's it, you guys are awesome!
Excelchat Expert
16/10/2018 - 04:10
I'm glad to hear that.
Excelchat Expert
16/10/2018 - 04:10
Looking forward to have you with is in future.
Excelchat Expert
16/10/2018 - 04:10
Thanks for coming to Excelchat. Feel free to leave any comments or feedback. Have a nice day!
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.
