Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copy unique data
This may be a little confusing...to a beginner, me.
Instead of entering data in two worksheets can I: In worksheet A a1 b1 c1 d1 date # time place 20 oct 77 940 OH 21 oct 99 1240 NY 22 oct 101 745 OH 22 oct 128 545 PA 22 oct 222 655 NY In worksheet B a1 b1 c1 d1 date pa ny oh 20 oct 77 21 oct 99 22 oct 128 222 101 22 oct can be three seperate rows but would prefer one. Can this be done or am I stuck entering the data twice? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copy unique data
Your example doesn't explain what criteria you want to use to consolidate
your 22 oct rows. Dave -- Brevity is the soul of wit. "Sam" wrote: This may be a little confusing...to a beginner, me. Instead of entering data in two worksheets can I: In worksheet A a1 b1 c1 d1 date # time place 20 oct 77 940 OH 21 oct 99 1240 NY 22 oct 101 745 OH 22 oct 128 545 PA 22 oct 222 655 NY In worksheet B a1 b1 c1 d1 date pa ny oh 20 oct 77 21 oct 99 22 oct 128 222 101 22 oct can be three seperate rows but would prefer one. Can this be done or am I stuck entering the data twice? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copy unique data
Dave,
I want the data in worksheet A to be copied to worksheet B to the cells specified. Basically consolidate the like data; by place. If I can't have one row for 22 oct then I'll have to settle for 3 rows for that date and others with multiple inputs. Hope that clears things. "Dave F" wrote: Your example doesn't explain what criteria you want to use to consolidate your 22 oct rows. Dave -- Brevity is the soul of wit. "Sam" wrote: This may be a little confusing...to a beginner, me. Instead of entering data in two worksheets can I: In worksheet A a1 b1 c1 d1 date # time place 20 oct 77 940 OH 21 oct 99 1240 NY 22 oct 101 745 OH 22 oct 128 545 PA 22 oct 222 655 NY In worksheet B a1 b1 c1 d1 date pa ny oh 20 oct 77 21 oct 99 22 oct 128 222 101 22 oct can be three seperate rows but would prefer one. Can this be done or am I stuck entering the data twice? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copy unique data
No, it doesn't clear things. How do you determine that a given piece of data
in workbook A goes to a given cell in workbook B? YOU may understand that, but you haven't provided any information with which responders to this post can understand. -- Brevity is the soul of wit. "Sam" wrote: Dave, I want the data in worksheet A to be copied to worksheet B to the cells specified. Basically consolidate the like data; by place. If I can't have one row for 22 oct then I'll have to settle for 3 rows for that date and others with multiple inputs. Hope that clears things. "Dave F" wrote: Your example doesn't explain what criteria you want to use to consolidate your 22 oct rows. Dave -- Brevity is the soul of wit. "Sam" wrote: This may be a little confusing...to a beginner, me. Instead of entering data in two worksheets can I: In worksheet A a1 b1 c1 d1 date # time place 20 oct 77 940 OH 21 oct 99 1240 NY 22 oct 101 745 OH 22 oct 128 545 PA 22 oct 222 655 NY In worksheet B a1 b1 c1 d1 date pa ny oh 20 oct 77 21 oct 99 22 oct 128 222 101 22 oct can be three seperate rows but would prefer one. Can this be done or am I stuck entering the data twice? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copy unique data
Try this:
in Sheet 2 B2 =IF(ISERROR(MATCH(1,(Sheet1!$A$2:$A$6=Sheet2!$A2)* (Sheet1!$D$2:$D$6=Sheet2!B$1),0)),"",INDEX(Sheet1! $B$2:$B$6,MATCH(1,(Sheet1!$A$2:$A$6=Sheet2!$A2)*(S heet1!$D$2:$D$6=Sheet2!B$1),0))) ctrlshiftenter (not just enter) Copy accross and down as needed "Sam" wrote: This may be a little confusing...to a beginner, me. Instead of entering data in two worksheets can I: In worksheet A a1 b1 c1 d1 date # time place 20 oct 77 940 OH 21 oct 99 1240 NY 22 oct 101 745 OH 22 oct 128 545 PA 22 oct 222 655 NY In worksheet B a1 b1 c1 d1 date pa ny oh 20 oct 77 21 oct 99 22 oct 128 222 101 22 oct can be three seperate rows but would prefer one. Can this be done or am I stuck entering the data twice? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copy unique data
Okay, I'll be brief and precise.
Worksheet A contains headers: date, #, time, place. Data in (A2:D2): date, #, time, and OH, or NY, or PA Worksheet B contains headers: date, #, OH, NY, PA Data in (A2:E2) I would like to copy date, #, and place to the coressponding place name (OH, NY, PA) worksheet A A B C D 1 DATE # TIME PLACE 2 22 OCT 135 730 OH 3 23 OCT 155 945 NY 4 23 OCT 98 545 OH 5 24 OCT 101 730 PA worksheet B shoud look like this a b c d 1 DATE OH NY PA 2 22 oct 135 3 23 oct 98 155 4 24 oct 101 Worksheet resorts the data. Thanks in advance Sam "Dave F" wrote: No, it doesn't clear things. How do you determine that a given piece of data in workbook A goes to a given cell in workbook B? YOU may understand that, but you haven't provided any information with which responders to this post can understand. -- Brevity is the soul of wit. "Sam" wrote: Dave, I want the data in worksheet A to be copied to worksheet B to the cells specified. Basically consolidate the like data; by place. If I can't have one row for 22 oct then I'll have to settle for 3 rows for that date and others with multiple inputs. Hope that clears things. "Dave F" wrote: Your example doesn't explain what criteria you want to use to consolidate your 22 oct rows. Dave -- Brevity is the soul of wit. "Sam" wrote: This may be a little confusing...to a beginner, me. Instead of entering data in two worksheets can I: In worksheet A a1 b1 c1 d1 date # time place 20 oct 77 940 OH 21 oct 99 1240 NY 22 oct 101 745 OH 22 oct 128 545 PA 22 oct 222 655 NY In worksheet B a1 b1 c1 d1 date pa ny oh 20 oct 77 21 oct 99 22 oct 128 222 101 22 oct can be three seperate rows but would prefer one. Can this be done or am I stuck entering the data twice? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copy unique data
Doesn't work. Entered the array in Sheet 2 B2. Nothing was copied from
sheet 1 to sheet 2 or 2 to 1 if I messed it up some how :) "Teethless mama" wrote: Try this: in Sheet 2 B2 =IF(ISERROR(MATCH(1,(Sheet1!$A$2:$A$6=Sheet2!$A2)* (Sheet1!$D$2:$D$6=Sheet2!B$1),0)),"",INDEX(Sheet1! $B$2:$B$6,MATCH(1,(Sheet1!$A$2:$A$6=Sheet2!$A2)*(S heet1!$D$2:$D$6=Sheet2!B$1),0))) ctrlshiftenter (not just enter) Copy accross and down as needed "Sam" wrote: This may be a little confusing...to a beginner, me. Instead of entering data in two worksheets can I: In worksheet A a1 b1 c1 d1 date # time place 20 oct 77 940 OH 21 oct 99 1240 NY 22 oct 101 745 OH 22 oct 128 545 PA 22 oct 222 655 NY In worksheet B a1 b1 c1 d1 date pa ny oh 20 oct 77 21 oct 99 22 oct 128 222 101 22 oct can be three seperate rows but would prefer one. Can this be done or am I stuck entering the data twice? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copy unique data
Tested and it works.
"Sam" wrote: Doesn't work. Entered the array in Sheet 2 B2. Nothing was copied from sheet 1 to sheet 2 or 2 to 1 if I messed it up some how :) "Teethless mama" wrote: Try this: in Sheet 2 B2 =IF(ISERROR(MATCH(1,(Sheet1!$A$2:$A$6=Sheet2!$A2)* (Sheet1!$D$2:$D$6=Sheet2!B$1),0)),"",INDEX(Sheet1! $B$2:$B$6,MATCH(1,(Sheet1!$A$2:$A$6=Sheet2!$A2)*(S heet1!$D$2:$D$6=Sheet2!B$1),0))) ctrlshiftenter (not just enter) Copy accross and down as needed "Sam" wrote: This may be a little confusing...to a beginner, me. Instead of entering data in two worksheets can I: In worksheet A a1 b1 c1 d1 date # time place 20 oct 77 940 OH 21 oct 99 1240 NY 22 oct 101 745 OH 22 oct 128 545 PA 22 oct 222 655 NY In worksheet B a1 b1 c1 d1 date pa ny oh 20 oct 77 21 oct 99 22 oct 128 222 101 22 oct can be three seperate rows but would prefer one. Can this be done or am I stuck entering the data twice? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copy unique data
Hi,
sheet 1 has headers in a1:d1 date, #, time, place sheet 2 has headers in a1:d1 date, pa, ny, oh Okay. I recopied the if(iserror.... into sheet 2 B2 ctr - shift - enter entered data in sheet 1 a2:d4 A2:A4 22 oct etc B2:B4 # 555 etc C2:C4 time 730 etc D2:D4 place oh, ny etc Sheet 2 now has 555 in b2:b4 correct # except its repeated 111 in c2:c4 correct # except its repeated but doesn't show the other # 222 122 in d2:d4 correct except its repeated and no date copied to a2:a4 Is this possible or am I out of luck and just need to input the data in two sheets? Thanks for your help. "Teethless mama" wrote: Tested and it works. "Sam" wrote: Doesn't work. Entered the array in Sheet 2 B2. Nothing was copied from sheet 1 to sheet 2 or 2 to 1 if I messed it up some how :) "Teethless mama" wrote: Try this: in Sheet 2 B2 =IF(ISERROR(MATCH(1,(Sheet1!$A$2:$A$6=Sheet2!$A2)* (Sheet1!$D$2:$D$6=Sheet2!B$1),0)),"",INDEX(Sheet1! $B$2:$B$6,MATCH(1,(Sheet1!$A$2:$A$6=Sheet2!$A2)*(S heet1!$D$2:$D$6=Sheet2!B$1),0))) ctrlshiftenter (not just enter) Copy accross and down as needed "Sam" wrote: This may be a little confusing...to a beginner, me. Instead of entering data in two worksheets can I: In worksheet A a1 b1 c1 d1 date # time place 20 oct 77 940 OH 21 oct 99 1240 NY 22 oct 101 745 OH 22 oct 128 545 PA 22 oct 222 655 NY In worksheet B a1 b1 c1 d1 date pa ny oh 20 oct 77 21 oct 99 22 oct 128 222 101 22 oct can be three seperate rows but would prefer one. Can this be done or am I stuck entering the data twice? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting 2 colums of numbers and incremening them down | Excel Discussion (Misc queries) | |||
Retrieve multiple data rows data from a very long list and copy t | Excel Discussion (Misc queries) | |||
ranking query | Excel Discussion (Misc queries) | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
How do i copy columns of data in notepad into microsoft excel? | Excel Discussion (Misc queries) |