• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How do I pull data from a long, long list?

E

eds

New Member
Hi everyone, I was just wondering is there a simple way to pull rows of entry from a large list.


So I have this long lists of a product. The list consists of the product's serial number, and its test results (It's the same product). This list is a product certificate from the factory, that the customers usually request when purchasing the product, and when purchasing, the product isn't sorted at all - so in a purchase, they can have serial numbers that are all over the place.


So what I do is, when someone makes a purchase, I look it up using the search function by the serial number, and copy the entry (essentially the whole row) one by one, and paste in in a new excel sheet. Now, the good news is that my sales volume is growing, the bad news is, I can't keep on doing it the way I'm doing it now - I'd spend too much time finding and copy-pasting.


Essentially, is there a way that I could somehow type the serial number of the product in one column, and the corresponding entry immediately shows up on the columns next to it?
 
Hi ,


Certainly. Can you give more details or upload a sample workbook or even a .jpg snapshot of your data ? The way to upload a file is explained here :


http://chandoo.org/forums/topic/posting-a-sample-workbook


Narayan
 
Hi.. Eds,


I think you can use Vlookup or Index,match Functions for this. Assume that, in cell A1 if you entered Product Id, then you can use index match function for every cell in your rows. After completion you can just copy down the formula. Hope it will helpful to you.


Thanks

Vijay
 
Thanks for the reply everyone! Here is a sample of the list:


https://www.dropbox.com/s/3a1xjoppjnbqyzp/sample.xls


I've just taken a short sample list. The original is much longer than that, unfortunately.


So what can I do, so that when I type the serial number at a fresh excel sheet/document, the corresponding data shows up immediately? I'm thinking may I need some sort of database capabilities here?


@vijay.vizzu How would I do that? I don't know much about excel at all (sorry, excel newbie here)
 
eds,


Using Vijay.vizzu's idea, your formula would be something like:

=INDEX('Data Sheet'!B:B,MATCH(ProductCode,'Data Sheet'!$A:$A,0))


I'm assuming col A of your Data sheet contains the product ID's. ProductCode should be replace with a cell reference to wherever you are entering the data. So, let say you input "Cool Toy" in A2. In B2, formula is:

=INDEX('Data Sheet'!B:B,MATCH($A2,'Data Sheet'!$A:$A,0))


Copy this formula to the right to return all the information about "Cool Toy" from the "Data Sheet" worksheet.
 
Hi ,


Check out the workbook at the following link :


https://docs.google.com/open?id=0B0KMpuzr3MTVeHFyd0xXNzlJVUU


Note that the formulae have been entered as array formulae , by selecting the entire set of relevant columns ( column B through column L e.g. B4:L4 ) , and entering the formula , and then pressing CTRL SHIFT ENTER. Hence the formula will be identical in all the cells B4 through L4.


I have copied this array formula from the range B4:L4 till B33:L33.


In case you need to copy the formula further down , you will have to select the range B33:L33 , and copy this range downwards.


Also , the input data range has been taken as whatever you have at present i.e. A4:L22 ; as and when you add more data , you will have to modify all of the formulae on Sheet2 , starting from row 4 i.e. B4:L4.


Narayan
 
Good day all

I am probably on the wrong track here and would appreciate it if those who know more would point out the error in my thinking,,,,but could eds just turn his spread sheet in to a table, he could then search any column for any critrea. select all that match and copy to new spread sheet
 
Thanks Narayank991! It's working! But can you help me by explaining the logic behind the formula?


From what I see, it uses the index and match function just like vijay.vizzu suggested. But how do you select the data to be indexed and matched?


@bobhc can you explain how to do that too?


I'm getting the impression excel is more complicated than the universe... (just kidding...)
 
Hi ,


Can you check out the following links ?


1. http://www.grbps.com/Excel6.pdf


2. http://www.exceluser.com/explore/functions.htm


3. http://www.learntia.com/microsoft-excel-match-and-index-tutorial-video-132.html


In the meantime , I'll try to put together some notes on what has been used in the uploaded workbook.


Narayan
 
eds..what version of excel are you using, I am on 2010 and to do what I posted I just selected a cell in the current spread sheet choose insert from the ribbon and then table...job done
 
@bobhc I'm using excel 2008. I didn't have the insert>table function? All I have is Data>table, and even then I don't understand how the input row is supposed to be working.


Anyway, I'll read up on it later. Thanks everyone!
 
Eds


Insert Table is not the same as Data Table


Insert Table converts a normal Range to a special Named Range, which has special properties like being able to expand as data is added and maintain Headings, Summary and Row formats


Data Table is an analysis tool for running multiple solutions from a model subject to multipel inputs.
 
You must log in or register to reply here.
Back
Top