I need Excel Help badly

[robisc]

OK I seriously need Excel help, this will make my life easier, I know what I want to do but not how to do it, and it should be something that is possible. There is a sample of my spreadsheet below with only 3 rows and 6 columns, columns A, B and C are a range in a specific sort order that I don't want to change, columns D, E and F are basically the same data with more or less info attached that was exported from a SQL database and I need to make columns D, E and F match coumn A, can this be done?? If anyone's still with me then here's the tricky part, keep in mind that there may be more or less data in column A that is not in column D which means that if I build a custom sort order and sort D using a custom sort order of column A, the sort does work but the rows for D don't necessarily match column A and I want them to match so if there were a value for A that wasn't in column D list then I want a blank line to be put there. If I was as clear as mud and someone has a solution I would really appreciate it.



RA4062256 BARAGONA_L 5424-151 RA3343435 268443 SILICONE
RA4062287 BARAGONA_L 5424-151 RA3343436 268444 SILICONE
RA4062254 BARAGONA_L 5424-151 RA3343441 268449 SILICONE

 

[WW]

so columns A and D will be the same? so in your example, somewhere in D, RA4062256 will show up with data in E and F that you want to tie together with Column A (and B and C)?

why not fix the query from the SQL database to use that relation? to output the matched data in one line.

if that is impossible, the VLOOKUP function would do what you want. Use column D as your column to match with A, then have a vlookup that outputs the value in E and F.

take a look at the help file, post here if you need some assistance.

or if that isn't what you want, please clarify.

WW

 

[eklass]

eek, i'm excel-ified and sql-ified, but i'm confus-ified mostly by your post

 

[KLin]

Originally posted by: robisc
OK I seriously need Excel help, this will make my life easier, I know what I want to do but not how to do it, and it should be something that is possible. There is a sample of my spreadsheet below with only 3 rows and 6 columns, columns A, B and C are a range in a specific sort order that I don't want to change, columns D, E and F are basically the same data with more or less info attached that was exported from a SQL database and I need to make columns D, E and F match coumn A, can this be done?? If anyone's still with me then here's the tricky part, keep in mind that there may be more or less data in column A that is not in column D which means that if I build a custom sort order and sort D using a custom sort order of column A, the sort does work but the rows for D don't necessarily match column A and I want them to match so if there were a value for A that wasn't in column D list then I want a blank line to be put there. If I was as clear as mud and someone has a solution I would really appreciate it.



RA4062256 BARAGONA_L 5424-151 RA3343435 268443 SILICONE
RA4062287 BARAGONA_L 5424-151 RA3343436 268444 SILICONE
RA4062254 BARAGONA_L 5424-151 RA3343441 268449 SILICONE

can you post the whole spreadsheet?

 

[robisc]

OK let me try and clarify, I have the first 3 columns with say roughly 5000 rows in it

I then have 3 additional columns with other data, column D will have some, not all of the same data as column A.

I need to match and line up columns D, E and F with the data that mathes in D and in column A, the data on the right and their respective data with the names and data on the left.

So in other words let's say the data on the right side (column D thru E) has only 1000 of the 5000 names from column A after they are matched and sorted their will be some blank lines between the rows as they line up with the names and data on the left.

Let me try and give an example:

So the list on the left (A) here contains all the names while the list on the right (D) has only 1000 of those names. I need to sort the list on the right to line up with the names on the left.

Another example:

A B C D E F
RA4062256 BARAGONA_L 5424-151 RA4062254 268443 SILICONE
RA4062287 BARAGONA_L 5424-151 RA3757693 268444 SILICONE
RA4062254 BARAGONA_L 5424-151 RA4062256 268449 SILICONE
RA3757693 BARAGONE_L 5424-151


So column A must stay in this order and the rows for B and C must match A, now lets's say this was my complete spreadsheet and if I simply did a sort by using column A as the sort order for D, E and F my output rows wouldn't match going across, it would look like this:

A B C D E F
RA4062256 BARAGONA_L 5424-151 RA4062256 268443 SILICONE
RA4062287 BARAGONA_L 5424-151 RA4062254 268443 SILICONE
RA4062254 BARAGONA_L 5424-151 RA3757693 268444 SILICONE
RA3757693 BARAGONE_L 5424-151

Output needs to look like this:

A B C D E F
RA4062256 BARAGONA_L 5424-151 RA4062256 268443 SILICONE
RA4062287 BARAGONA_L 5424-151
RA4062254 BARAGONA_L 5424-151 RA4062254 268443 SILICONE
RA3757693 BARAGONE_L 5424-151 RA3757693 268444 SILICONE


Hope this helps and thanks for replies : )

 

[bigoman]

I agree with WW in that vlookup is the way to go.


Take columns D-F and cut and paste them into a separate worksheet (we will call this "data") in columns A B C.

Then put this in the first row in columns D E F in your original worksheet


Column D =vlookup(A1,Data!A:C,1,0)
Column E =vlookup(A1,Data!A:C,2,0)
Column F =vlookup(A1,Data!A:C,3,0)

Then you can just clickdrag the 3 cells all the way down. If something was in A B C on the original worksheet that wasnt in D E F, then it will show up as #N/A. You can use a simple find/replace to make those blanks.

If you are confused, or want me to do it for you, I would be happy to.

Just PM me, and I'll give you my email. Or post the Spreadsheet.

 

[robisc]

bigoman and WW, you are the men, Thanks. If you were here right now I would kiss ya, well no maybe not, seriously though THANKS a million. : )
Ty

 

[bigoman]

no problem

I'm at work right now where I'm using.. excel.. so yeah, its not a huge stretch for me to go help you =)

Better than doing real work, at least.