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?

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?

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?

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?

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?

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?

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?

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?

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?