Excel Issue

MisterCadbury

I am trying to analyse and sort a couple of arrays of data but I can't get one piece of the formula to work out, so any help you could offer would be greater appreciated.

Basically I have a 3 columns of data. Column A is a list of each data point name and ranges from xx0001 to xx6000. Column B contains 100 random entries that correspond to the data names in column A but they are unordered. Column C then contains values that correspond to the data point names in column B. What I want to do is arrange the data in column B and C so that it is in the same order as column A. So for example data point name ( Column and data point value ( Column C) for the 100 values would be matched to the corresponding data point names in column A.

Any help with this would be much appreciated

FSL

You need to be a little more explicit.

I read it as columns A and C are data point names, column B contains a value relating to the data point name in column C.

Consequently column A is redundant and sorting columns B,C on column C will sort the data into data point name order.

If that was the case then you wouldn't be asking the question hence my request for more explicit information.

Or are you saying column A is a complete list of data point names. Columns B and C are the value, name pair in random order but there are not necessarily a one for one match in the names and you want to order B,C in name order with blank rows where there is a name in column A but not a matching one in C?

MisterCadbury

My apologies I am not explaining it well, I have 6000 names in column A (xx0001 to xx6000), and in column be I have a random collection of 100 of the names in column B ( xx0008, xx0156 etc). Column C feature numbers that correspond to the names in column B. I have done the first part myself which was to align the names in column B to the corresponding name in column A in a new column , column D( So for example in cell name xx0667 in A is now matched to cell xx0667 in column D as XX0667 featured in the random list in column using the following =IF(ISNA(MATCH(A51,$B$2:$B$353,0)),"",INDEX($A$2:$B$353,MATCH(A51,$B$2:$B$353,0),2))

The trouble arises when I want the values in column C to be spread out correctly in such a was that a specific cell in column A has the name xx0999 which if it is present in the random list in column B then it and its corresponding value in column C now features in the newly created columns D and E.

I have attached an imaged to hopefully make it easier to get across what I am trying to. Thanks very much for having a look.

Procasinator

I don't fully understand what you are trying to do here. It kind of sounds like you want a VLOOKUP.

MisterCadbury

Procasinator wrote: »

I don't fully understand what you are trying to do here. It kind of sounds like you want a VLOOKUP.

In the screen grab, I want in column E, the value that corresponds xx0130 in column C.

Any advice much appreciated

MagicMarker

I've read this about 10 times and still can't figure out what you're trying to do.

MisterCadbury

MagicMarker wrote: »

I've read this about 10 times and still can't figure out what you're trying to do.

I guess I am not great at explaining but If you pinpoint where I am losing you I might be able to clarify abit better or am I losing you from the start??

I want to essentially align the data in Column B and C (Which are linked, name in column B, values in column C) to that of column A .

Muppet Man

Crikey have to admit, I am not getting it either.

Show is a screen shot with just 5 or 6 rows of data with columns A to E filled in by hand....

Forget about trying to describe the problem in terms of formulas, and just show us what you need it to look like

Muppet man

croo

try using the vlookup function, though it might not be very efficient it should work.

ps. e.g. vlookup($A2,B:C,1,FALSE) in the D column & vlookup($A2,B:C,2,FALSE) in the E colunm

MisterCadbury

Croo, genuinely thank you very much, your vlookups work exactly how I want only issue is I get alot of #N/A but I can get rid of them easily with the replace function.

You have shaved hours off what I was trying to do.

Thanks again

croo

MisterCadbury wrote: »

Croo, genuinely thank you very much, your vlookups work exactly how I want only issue is I get alot of #N/A but I can get rid of them easily with the replace function.

You have shaved hours off what I was trying to do.

Thanks again

Wrap the vlookup in an iferror with a blank ("") value to get rid of the #n/a

PS. Procrastinator did suggest a vlookup earlier!

MagicMarker

I'm actually puzzled how you know about IF, ISNA, INDEX, MATCH functions but VLOOKUP didn't occur to you??