copy unique data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sam - 24 Oct 2006 17:53 GMT

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 to this Message

Dave F - 24 Oct 2006 17:57 GMT

Your example doesn't explain what criteria you want to use to consolidate
your 22 oct rows.

Dave

Signature

Brevity is the soul of wit.

> This may be a little confusing...to a beginner, me.
> Instead of entering data in two worksheets can I:
[quoted text clipped - 21 lines]
>
> Can this be done or am I stuck entering the data twice?

Reply to this Message

Sam - 24 Oct 2006 18:09 GMT

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.

> Your example doesn't explain what criteria you want to use to consolidate
> your 22 oct rows.
[quoted text clipped - 26 lines]
> >
> > Can this be done or am I stuck entering the data twice?

Reply to this Message

Dave F - 24 Oct 2006 18:12 GMT

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.

Signature

Brevity is the soul of wit.

> Dave,
>
[quoted text clipped - 33 lines]
> > >
> > > Can this be done or am I stuck entering the data twice?

Reply to this Message

Sam - 24 Oct 2006 19:06 GMT

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

> 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,
[quoted text clipped - 38 lines]
> > > >
> > > > Can this be done or am I stuck entering the data twice?

Reply to this Message

Teethless mama - 24 Oct 2006 18:43 GMT

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)*(Sheet1!$D$2:$D$6=Sheet2!B$1),0)))

ctrl>shift>enter (not just enter)
Copy accross and down as needed

> This may be a little confusing...to a beginner, me.
> Instead of entering data in two worksheets can I:
[quoted text clipped - 21 lines]
>
> Can this be done or am I stuck entering the data twice?

Reply to this Message

Sam - 24 Oct 2006 20:32 GMT

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 :)

> Try this:
>
[quoted text clipped - 29 lines]
> >
> > Can this be done or am I stuck entering the data twice?

Reply to this Message

Teethless mama - 24 Oct 2006 20:52 GMT

Tested and it works.

> 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 :)
[quoted text clipped - 32 lines]
> > >
> > > Can this be done or am I stuck entering the data twice?

Reply to this Message

Sam - 24 Oct 2006 21:19 GMT

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.

> Tested and it works.
>
[quoted text clipped - 34 lines]
> > > >
> > > > Can this be done or am I stuck entering the data twice?

Reply to this Message