I have 6 columns of data. 2 of the columns are called ID and there are
thousuands of id numbers in both columns, but currently do not match up. I
want both id columns to have the same id in a row. So for example row 2
should have id#6 in column A and its information related to it in B & C as
well as Column D having id#6 and the 2 columns associatd with that id (as
well as the other information that those ids were associated with).
How do I go about doing this?

Thanks,
~JJ44

If sorting rows A-C and then doing the same for rows D-F (sorted by id#)
doesn't do it, then you might consider some helper columns.

Helper Columns H, I, & J.

Assuming your data starts at A2.
In H2, enter:
=A2
In I2, Enter:
=INDIRECT("E"&MATCH($H14,D:D,0))
In J2, Enter:
=INDIRECT("F"&MATCH($H14,D:D,0))

Fill down these three formulas as far as needed.
Then you can copy the data in those three columns and paste special-Values
beginning at D2.

HTH,
Paul

"JJ44" wrote in message
...
I have 6 columns of data. 2 of the columns are called ID and there are
thousuands of id numbers in both columns, but currently do not match up. I
want both id columns to have the same id in a row. So for example row 2
should have id#6 in column A and its information related to it in B & C as
well as Column D having id#6 and the 2 columns associatd with that id (as
well as the other information that those ids were associated with).
How do I go about doing this?

Thanks,
~JJ44

Sorting will not work as there will not be a match for every item...I am not
really sure what the helper columns are trying to do for me or how that will
even help. I also am not exactly sure how to use them. What does the indirect
function even do and the cells you are referring to in the formula? Here is
an example of what I have to work with:
And I would want to get id 1 with a cost of 20 and quantity 1 (first 3
columns) in the same row as id 1, cost of 18 and quantity of 1 (last 3
columns).

ID Cost Quantity ID Cost Quantity
1 20 1 5 75 2
2 30 1 8 100 3
6 35 1 3 122 3
4 50 2 7 30 3
5 67 2 2 30 1
8 85 3 4 65 2
7 45 3 1 18 1
3 33 3 6 50 1
10 55 5
20 100 5

Hope this is explained better.
John

"PCLIVE" wrote:

If sorting rows A-C and then doing the same for rows D-F (sorted by id#)
doesn't do it, then you might consider some helper columns.

Helper Columns H, I, & J.

Assuming your data starts at A2.
In H2, enter:
=A2
In I2, Enter:
=INDIRECT("E"&MATCH($H14,D:D,0))
In J2, Enter:
=INDIRECT("F"&MATCH($H14,D:D,0))

Fill down these three formulas as far as needed.
Then you can copy the data in those three columns and paste special-Values
beginning at D2.

HTH,
Paul

"JJ44" wrote in message
...
I have 6 columns of data. 2 of the columns are called ID and there are
thousuands of id numbers in both columns, but currently do not match up. I
want both id columns to have the same id in a row. So for example row 2
should have id#6 in column A and its information related to it in B & C as
well as Column D having id#6 and the 2 columns associatd with that id (as
well as the other information that those ids were associated with).
How do I go about doing this?

Thanks,
~JJ44

Ok,

The helper columns are basically temporary columns that you setup in order
to come to the results needed. Find 3 available consecutive columns off to
the right of your data. I've used columns H, I, and J in my example. In my
previous example, my formula would have produced #N/A if the id in column A
was not found in column D. So I've taken care of that in these formulas.
Again, this assumes that you have a header row and your data begins at A2.

In cell H2, enter this formula:
=A2
In cell I2, enter this formula:
=IF(ISERROR(MATCH($H2,D:D,0)),"",INDIRECT("E"&MATC H($H2,D:D,0)))
In cell J2, enter this formula:
=IF(ISERROR(MATCH($H2,D:D,0)),"",INDIRECT("F"&MATC H($H2,D:D,0)))


Copy these formulas down as many rows down as your data in column A.
The MATCH function returns the row number of a value found in a designated
range. So the formula looks for the data from H2 (technically A2) from
within the range of column D. Since it only returns the row number, then we
use INDIRECT to return a value in a specified cell beginning with the column
letter and then the row number that was determined using the MATCH function.

HTH,
Paul



"JJ44" wrote in message
...
Sorting will not work as there will not be a match for every item...I am
not
really sure what the helper columns are trying to do for me or how that
will
even help. I also am not exactly sure how to use them. What does the
indirect
function even do and the cells you are referring to in the formula? Here
is
an example of what I have to work with:
And I would want to get id 1 with a cost of 20 and quantity 1 (first 3
columns) in the same row as id 1, cost of 18 and quantity of 1 (last 3
columns).

ID Cost Quantity ID Cost Quantity
1 20 1 5 75 2
2 30 1 8 100 3
6 35 1 3 122 3
4 50 2 7 30 3
5 67 2 2 30 1
8 85 3 4 65 2
7 45 3 1 18 1
3 33 3 6 50 1
10 55 5
20 100 5

Hope this is explained better.
John

"PCLIVE" wrote:

If sorting rows A-C and then doing the same for rows D-F (sorted by id#)
doesn't do it, then you might consider some helper columns.

Helper Columns H, I, & J.

Assuming your data starts at A2.
In H2, enter:
=A2
In I2, Enter:
=INDIRECT("E"&MATCH($H14,D:D,0))
In J2, Enter:
=INDIRECT("F"&MATCH($H14,D:D,0))

Fill down these three formulas as far as needed.
Then you can copy the data in those three columns and paste
special-Values
beginning at D2.

HTH,
Paul

"JJ44" wrote in message
...
I have 6 columns of data. 2 of the columns are called ID and there are
thousuands of id numbers in both columns, but currently do not match
up. I
want both id columns to have the same id in a row. So for example row
2
should have id#6 in column A and its information related to it in B & C
as
well as Column D having id#6 and the 2 columns associatd with that id
(as
well as the other information that those ids were associated with).
How do I go about doing this?

Thanks,
~JJ44