creating matching data in different columns

I have a problem creating the look of the spreadsheet I want. I have information in column A that needs to match information in columns B through Z. But in B, not all the information is the same.

In my sheet 1, in column A I have a list of letters/numbers that I need to line up with other columns across my spreadsheet. For example, column A contains a number like 08.01A, 08.01B, 08.01C, 08.24C. Column B doesn't contain 08.01C but contains the other numbers, so I want a blank line in that space instead. I would like for each of my columns to match in length, filling in blanks when the information is not there, so I can look across the different columns and have the rows match. I have all the information in the columns and don't what to have to insert the blank cells by hand if the info doesn't match across the row. There are way too many columns to match up.

Does anyone know what I can do?

Please help, I am Excel illiterate when it comes to working with spreadsheets.

One approach to this would be to insert "helper columns in between the
actual columns of data.
You could then *hide* the real columns, and the "helper' columns would
present the display that you are looking for.

For example:
Column A = "Main DataList"
Column B = "Partial Data"

Insert a *new* column B.
Original column B is now column C, containing the "PartialData".

Enter this in the new column B:

=IF(ISNA(MATCH(A1,$C$1:$C$100,0)),"",INDEX($C$1:$C $100,MATCH(A1,$C$1:$C$100,
0)))

And copy down as needed.

Now hide column C, and you should have the display that you wish.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Tig" wrote in message
...
I have a problem creating the look of the spreadsheet I want. I have
information in column A that needs to match information in columns B through
Z. But in B, not all the information is the same.

In my sheet 1, in column A I have a list of letters/numbers that I need to
line up with other columns across my spreadsheet. For example, column A
contains a number like 08.01A, 08.01B, 08.01C, 08.24C. Column B doesn't
contain 08.01C but contains the other numbers, so I want a blank line in
that space instead. I would like for each of my columns to match in length,
filling in blanks when the information is not there, so I can look across
the different columns and have the rows match. I have all the information in
the columns and don't what to have to insert the blank cells by hand if the
info doesn't match across the row. There are way too many columns to match
up.

Does anyone know what I can do?

Please help, I am Excel illiterate when it comes to working with
spreadsheets.

RD, that worked very well, I do appreciate it. I do have a few more questions though. When I copied the formula into the cell and then down, it worked. But when I tried to do the same for my other columns, I got the same information as in the first column instead of the new information. What did I do wrong?

Also, is there a way to add up how many instances a number appears in the row? I need to document how many times the same number shows up across the row, and instead of counting them all one at a time, I was thinking that it would be easier if there was a formula for that as well. That way, if something were to change, I wouldn't have to count all over again.

Thanks for the help, it's making this go much easier.

"RagDyer" wrote:

One approach to this would be to insert "helper columns in between the
actual columns of data.
You could then *hide* the real columns, and the "helper' columns would
present the display that you are looking for.

For example:
Column A = "Main DataList"
Column B = "Partial Data"

Insert a *new* column B.
Original column B is now column C, containing the "PartialData".

Enter this in the new column B:

=IF(ISNA(MATCH(A1,$C$1:$C$100,0)),"",INDEX($C$1:$C $100,MATCH(A1,$C$1:$C$100,
0)))

And copy down as needed.

Now hide column C, and you should have the display that you wish.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Tig" wrote in message
...
I have a problem creating the look of the spreadsheet I want. I have
information in column A that needs to match information in columns B through
Z. But in B, not all the information is the same.

In my sheet 1, in column A I have a list of letters/numbers that I need to
line up with other columns across my spreadsheet. For example, column A
contains a number like 08.01A, 08.01B, 08.01C, 08.24C. Column B doesn't
contain 08.01C but contains the other numbers, so I want a blank line in
that space instead. I would like for each of my columns to match in length,
filling in blanks when the information is not there, so I can look across
the different columns and have the rows match. I have all the information in
the columns and don't what to have to insert the blank cells by hand if the
info doesn't match across the row. There are way too many columns to match
up.

Does anyone know what I can do?

Please help, I am Excel illiterate when it comes to working with
spreadsheets.

You have to watch out for your relative and absolute (cell) references.

I'm assuming all columns are being compared to column A.
So, we can revise the the reference to column A and make it absolute. i.e.,
[$A1]
This allows you to copy *across* the columns, and still retain the column A
address and still allows the row address to change when you copy *down* the
column.

We do the opposite with the cell addresses that pertain to the "PartialData"
columns.
We revise the column references to relative, allowing them to change as you
copy *across* the columns, and retain the absolute row references, so that
they *don't* change as you copy down the columns. i.e., [C$1]

So, now the *original* formula should look like this:


=IF(ISNA(MATCH($A1,C$1:C$100,0)),"",INDEX(C$1:C$10 0,MATCH($A1,C$1:C$100,0)))

Enter this in the *new* column B.
When you *copy* this into the *new* column D, it should give you the proper
references and return the correct data.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Tig" wrote in message
...
RD, that worked very well, I do appreciate it. I do have a few more
questions though. When I copied the formula into the cell and then down, it
worked. But when I tried to do the same for my other columns, I got the same
information as in the first column instead of the new information. What did
I do wrong?

Also, is there a way to add up how many instances a number appears in the
row? I need to document how many times the same number shows up across the
row, and instead of counting them all one at a time, I was thinking that it
would be easier if there was a formula for that as well. That way, if
something were to change, I wouldn't have to count all over again.

Thanks for the help, it's making this go much easier.

"RagDyer" wrote:

One approach to this would be to insert "helper columns in between the
actual columns of data.
You could then *hide* the real columns, and the "helper' columns would
present the display that you are looking for.

For example:
Column A = "Main DataList"
Column B = "Partial Data"

Insert a *new* column B.
Original column B is now column C, containing the "PartialData".

Enter this in the new column B:


=IF(ISNA(MATCH(A1,$C$1:$C$100,0)),"",INDEX($C$1:$C $100,MATCH(A1,$C$1:$C$100,
0)))

And copy down as needed.

Now hide column C, and you should have the display that you wish.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Tig" wrote in message
...
I have a problem creating the look of the spreadsheet I want. I have
information in column A that needs to match information in columns B
through
Z. But in B, not all the information is the same.

In my sheet 1, in column A I have a list of letters/numbers that I need to
line up with other columns across my spreadsheet. For example, column A
contains a number like 08.01A, 08.01B, 08.01C, 08.24C. Column B doesn't
contain 08.01C but contains the other numbers, so I want a blank line in
that space instead. I would like for each of my columns to match in
length,
filling in blanks when the information is not there, so I can look across
the different columns and have the rows match. I have all the information
in
the columns and don't what to have to insert the blank cells by hand if
the
info doesn't match across the row. There are way too many columns to match
up.

Does anyone know what I can do?

Please help, I am Excel illiterate when it comes to working with
spreadsheets.

Hi,

I am having the same problem, have you figured out a way
to do it?

Cheers.


-----Original Message-----
I have a problem creating the look of the spreadsheet I
want. I have information in column A that needs to match
information in columns B through Z. But in B, not all the
information is the same.

In my sheet 1, in column A I have a list of
letters/numbers that I need to line up with other columns
across my spreadsheet. For example, column A contains a
number like 08.01A, 08.01B, 08.01C, 08.24C. Column B
doesn't contain 08.01C but contains the other numbers, so
I want a blank line in that space instead. I would like
for each of my columns to match in length, filling in
blanks when the information is not there, so I can look
across the different columns and have the rows match. I
have all the information in the columns and don't what to
have to insert the blank cells by hand if the info doesn't
match across the row. There are way too many columns to
match up.

Does anyone know what I can do?

Please help, I am Excel illiterate when it comes to
working with spreadsheets.
.

To do what, exactly?
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

"Tiff" wrote in message
...
Hi,

I am having the same problem, have you figured out a way
to do it?

Cheers.


-----Original Message-----
I have a problem creating the look of the spreadsheet I
want. I have information in column A that needs to match
information in columns B through Z. But in B, not all the
information is the same.

In my sheet 1, in column A I have a list of
letters/numbers that I need to line up with other columns
across my spreadsheet. For example, column A contains a
number like 08.01A, 08.01B, 08.01C, 08.24C. Column B
doesn't contain 08.01C but contains the other numbers, so
I want a blank line in that space instead. I would like
for each of my columns to match in length, filling in
blanks when the information is not there, so I can look
across the different columns and have the rows match. I
have all the information in the columns and don't what to
have to insert the blank cells by hand if the info doesn't
match across the row. There are way too many columns to
match up.

Does anyone know what I can do?

Please help, I am Excel illiterate when it comes to
working with spreadsheets.
.

I'm not quite sure if this is what you want, but assuming you have the
following 5 column table...

08.01A 08.01A 1 1 1
08.01B 08.01B 2 2 2
08.01C 08.24C 3 3 3
08.24C 08.01D 4 4 4
08.01D 08.24D 5 5 5
08.01E
08.24D
08.01F

Put the following formula in F1, copy across and down:

=IF(ISNA(MATCH($A1,$B:$B,0)),"",INDEX(B:B,MATCH($A 1,$B:$B,0)))

Then you should get the following table...

08.01A 08.01A 1 1 1
08.01B 08.01B 2 2 2
08.01C
08.24C 08.24C 3 3 3
08.01D 08.01D 4 4 4
08.01E
08.24D 08.24D 5 5 5
08.01F

Hope this helps!

In article ,
"Tiff" wrote:

Hi,

I am having the same problem, have you figured out a way
to do it?

Cheers.


-----Original Message-----
I have a problem creating the look of the spreadsheet I
want. I have information in column A that needs to match
information in columns B through Z. But in B, not all the
information is the same.

In my sheet 1, in column A I have a list of
letters/numbers that I need to line up with other columns
across my spreadsheet. For example, column A contains a
number like 08.01A, 08.01B, 08.01C, 08.24C. Column B
doesn't contain 08.01C but contains the other numbers, so
I want a blank line in that space instead. I would like
for each of my columns to match in length, filling in
blanks when the information is not there, so I can look
across the different columns and have the rows match. I
have all the information in the columns and don't what to
have to insert the blank cells by hand if the info doesn't
match across the row. There are way too many columns to
match up.

Does anyone know what I can do?

Please help, I am Excel illiterate when it comes to
working with spreadsheets.
.