• 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 to sort data from one sheet to another

S

Sudeep

New Member
Hi,

I want to sort data in two different sheets automatically

eg. If i have three sheets 1) Total Students 2) pass student 3) fail student

in first sheet i will write info like sr.no / Name / address / other info and pass or fail

and i want to enter all info automatically to other 2 sheets depending upon criteria of pass or fail.


Plz help me to solve the problem


Thanx...
 
i'd create a helper column on each worksheet


total student worksheet

the formula would be =countif($E$2:E2,E2)&E2

assuming line 1 is header and column E has the word "Pass" or "Fail"


Pass student worksheet

create a column and from line 2 copy down so that it shows "1pass", "2pass", "3pass" etc.


Fail student worksheet

create a column and from line 2 copy down so that it shows "1fail", "2fail", "3fail" etc.


the rest is a vlookup on the helper columns on Pass and Fail worksheet.
 
Hi Sudeep ,


An alternative is to use a filter on the Pass/Fail column , and copy the results to the respective sheets ; if you are not sure how to copy only the visible cells following a filter , check the link here :


http://office.microsoft.com/en-us/excel-help/copy-visible-cells-only-HA010244897.aspx


Alternatively , if you can upload your worksheet , I can do the job and reupload it.


Narayan
 
Thanx Fred, Thanx Narayan


Plz refer my uploaded file


https://hotfile.com/dl/152513804/7aae534/Sample_worksheet.xlsx.html


i dont want to copy by using filter is their any formula that cell automatically copied to another table or sheet


Sudeep
 
Hi Sudeep ,


I don't understand ; if there are formulae on the master data sheet , when each of the Pass and Fail portions of data is copied to their respective tabs , do you want the formulae to be copied or do you want only the values to be copied ?


Narayan
 
Hi Narayan

Thax for quick response.


I want values to be copied not the formulas.

when i enter Pass or fail i want all the info of student to be copied in respective sheet


Sudeep
 
Hi Sudeep,


I can't download you file for some reason...


Anyway, I guess you are looking for a live solution that when you enter pass/fail in summary sheet the other 2 worksheets will update automatically. You can use my method then.


all you need to do is to fill out the help columns on the other 2 worksheet and set up vlookup function. (use iferror when there is no entry because i don't think you want to see a bunch of #n/a)


once done all you need to worry is the entry on the summary page. The other 2 worksheets will update themselves. hope this help.
 
hey hi vijay,


dats work gr8 i want exactly what u sent can u plz tell me how u did this

i think u used VBA but i m not familiar with dat can u plz explain


Thanx

Sudeep
 
Hi, Sudeep!


If you want a non-VBA approach give a look at this file, just two helper columns:

http://dl.dropbox.com/u/60558749/how%20to%20sort%20data%20from%20one%20sheet%20to%20another%20%28for%20Sudeep%20at%20chandoo.org%29.xlsx


Please advise if anything not understandable.


Regards!
 
Hi JB,


Thanx

This VLookup formula also working gr8 but how can I apply if I have more columns and another data


regards,

Sudeep.
 
Hi, Sudeep!


It can be expanded to the right as needed with the only condition that the two first columns of Students sheet keep the structure (formulas and text to filter in first row) for splitting in two sheets.


If you have data to be to split in more worksheets, let's say 10, well, you'll need 10 auxiliary/helper columns instead of 2 at the left of first data column ("sr. no.") and 10 worksheets for splitting data.


If this is not what you were asking, please upload a sample file and manually set an example.


Regards!
 
You must log in or register to reply here.
Back
Top