Matching cells

[gcotterl]

My spreadsheet has "sections":

A1 thru N18513
O1 thru AC4841

If the contents of any cell in Col O and any cell in Col A are the
same, how can I keep the contents of the cells in both matching
"sections" (i.e., the cells in A thru N and the cells in O thru AC)?

 

[GS]

My spreadsheet has "sections":

A1 thru N18513
O1 thru AC4841

If the contents of any cell in Col O and any cell in Col A are the
same, how can I keep the contents of the cells in both matching
"sections" (i.e., the cells in A thru N and the cells in O thru AC)?

I'm guessing that what you want is to have cells A that have a match in
cells O to be lined up in the same row. Or are you asking something
different? You really need to be more precise about exactly what it is
you want.

 

[gcotterl]

Yes, I want to have cells A that have a match in cells O to be lined
up in the same row. (I tried describing this concept several ways
but couldn't come up with the right words).

 

[GS]

gcotterl laid this down on his screen :

Yes, I want to have cells A that have a match in cells O to be lined
up in the same row. (I tried describing this concept several ways
but couldn't come up with the right words).

Hi Gary,
Can you send me the file?

 

[GS]

gcotterl has brought this to us :

Yes, I want to have cells A that have a match in cells O to be lined
up in the same row. (I tried describing this concept several ways
but couldn't come up with the right words).

I ran ConditionalFormatting on $O:$O to place a border around each
dupe, and shading. There's lots!

The data appears to have unique values (ie: no dupes as far as I can
see) in each section. This means you won't have multiples of the same
value in the same section.

Both sections are sorted ascending. This would put the order of the 2nd
section as not sorted. Is this what you want? If so, I suggest to
locate (row position) 2nd section value in $A:$A, cut the cells in 2nd
section, insert (shift down) cut cells in 2nd section at found row
position.

 

[gcotterl]

gcotterl has brought this to us :


I ran ConditionalFormatting on $O:$O to place a border around each
dupe, and shading. There's lots!

The data appears to have unique values (ie: no dupes as far as I can
see) in each section. This means you won't have multiples of the same
value in the same section.

Both sections are sorted ascending. This would put the order of the 2nd
section as not sorted. Is this what you want? If so, I suggest to
locate (row position) 2nd section value in $A:$A, cut the cells in 2nd
section, insert (shift down) cut cells in 2nd section at found row
position.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

I can re-sort the 2nd section so the matches in both sections will be
lined up in the same row.

 

[gcotterl]

I can re-sort the 2nd section so the matches in both sections will be
lined up in the same row.- Hide quoted text -

- Show quoted text -

By the way, I'm using Microsoft Office Excel 2007.

 

[gcotterl]

gcotterl has brought this to us :


I ran ConditionalFormatting on $O:$O to place a border around each
dupe, and shading. There's lots!

The data appears to have unique values (ie: no dupes as far as I can
see) in each section. This means you won't have multiples of the same
value in the same section.

Both sections are sorted ascending. This would put the order of the 2nd
section as not sorted. Is this what you want? If so, I suggest to
locate (row position) 2nd section value in $A:$A, cut the cells in 2nd
section, insert (shift down) cut cells in 2nd section at found row
position.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

I don't know what you mean by "locate (row position) 2nd section value
in $A:$A, cut the cells in 2nd section, insert (shift down) cut cells
in 2nd section at found row
position".

 

[GS]

gcotterl expressed precisely :

I can re-sort the 2nd section so the matches in both sections will be
lined up in the same row.

Sorting the 2nd section just sorts its data. Since the first section
has more rows, the matches will not end up on the same row. The sort
result would be what you have now.

Also, lining up 2nd section dupes with 1st section rows means there
will be blanks in 2nd section.

 

[GS]

gcotterl wrote :

I don't know what you mean by "locate (row position) 2nd section value
in $A:$A, cut the cells in 2nd section, insert (shift down) cut cells
in 2nd section at found row
position".

You did say you want matching data in 2nd section to be on same row as
match in 1st section. That means the data in 2nd section has to be
repositioned to the same row as match in 1st section, right?

 

[gcotterl]

gcotterl wrote :







You did say you want matching data in 2nd section to be on same row as
match in 1st section. That means the data in 2nd section has to be
repositioned to the same row as match in 1st section, right?

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Right.

 

[gcotterl]

Are you suggesting that I should switch the two sections so:

Section 2 is in Cols A thru O
Section 1 in Cols P thru AC

If not, I don't understand your directions.

 

[gcotterl]

I've simplified my spreadsheet. It now has only two columns (A1 thru
A4841 and B1 thru B18513).

My question is: How do I eliminate the cells in Col B whose 19 left-
most characters do NOT match the 19 left-most characters in the cells
in Col A?

 

[GS]

gcotterl laid this down on his screen :

Are you suggesting that I should switch the two sections so:

Section 2 is in Cols A thru O
Section 1 in Cols P thru AC

If not, I don't understand your directions.

No! The arrangement is fine as is.

 

[GS]

I've simplified my spreadsheet. It now has only two columns (A1 thru
A4841 and B1 thru B18513).

My question is: How do I eliminate the cells in Col B whose 19 left-
most characters do NOT match the 19 left-most characters in the cells
in Col A?

Finally! We arive at what it is that you want (remove records).
Hooray!<g>

So then, is what you're saying is you want to end up with a list of
only matches where all non-matching records are remove from both
sections? (resulting with both sections having the same number of rows)

OR

Do you want to remove non-matches from Section1 (A1:A18513) only and
somehow align the matches with Section2?

 

[gcotterl]

I want to end up with a list of only matches where all non-matching
records are removed from both sections? (resulting with both sections
having the same number of rows)

 

[GS]

OR...
Are you looking for a solution to merely identify and highlight matches
so you can easily find them and view the data? This is what I did to
locate the matches in both sections (using CF). To view the matches
together I split the window vertically and scrolled either or both
panes to align whichever record I wanted.

OR...
In the case of a list of matches only, do you want the list put on a
separate worksheet so the original list remains intact?

 

[GS]

gcotterl laid this down on his screen :

I want to end up with a list of only matches where all non-matching
records are removed from both sections? (resulting with both sections
having the same number of rows)

Ok. See my other post about how/where to put the list.

 

[gcotterl]

It'd be best to put the list of only the matches on a separate
worksheet.

 

[gcotterl]

It looks like I have no other option than to MANUALLY move each cell
in Col A so it lines up with the matching cell in the Col B.