how to match and order a column according to values in another column

[Marwah]

Hello,

I have a column in(A )that i don't want to change and another column in (B) and Column (C)

I want to match and order column(B) according to column (A)
while changing the values of (C) to the changing in (B)

for example:

Column A

TCGA-A2-A3XY
TCGA-XX-A89A
TCGA-B6-A0I2

Please Login or Register  to view this content.

will change to be

Please Login or Register  to view this content.

and so on

how can I do that?

[FDibbins]

Hi, welcome to the forum

Not really sure I understand what you want here, but if you are looking for a formula way to sort text, thats not easy - which comes 1st, Friday or cat?

I suggest you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Marwah]

I have attached the data is it uploaded?

[Marwah]

so if you look at column A , I don't want to change
I want columns B to be the same as column A ( match and order)
and column C will change according to column B

[avk]

If "I2" is TCGA-A2-A3XY

In "L2"

Please Login or Register  to view this content.

In "M2"

Please Login or Register  to view this content.

Array formula [SHIFT+CTRL+ENTER]

[Marwah]

I'm confused now
I have uploaded the data with making I2= A2 and so on
the making L2=B2 and so on
M2=C2 and so on

Just look at my uploaded file

[FDibbins]

You wont be able to change B or C with formulas, you try this in a helper column (I used E and F...

	A	B	C	D	E	F
1
2	TCGA-A2-A3XY	TCGA-5L-AAT0	WHITE		TCGA-A2-A3XY	BLACK
3	TCGA-XX-A89A	TCGA-5L-AAT1	WHITE		TCGA-XX-A89A	WHITE
4	TCGA-B6-A0I2	TCGA-A1-A0SP	NA		TCGA-B6-A0I2	WHITE
5	TCGA-LL-A6FP	TCGA-A2-A04V	WHITE		TCGA-LL-A6FP	WHITE
6	TCGA-EW-A423	TCGA-A2-A04Y	WHITE		TCGA-EW-A423	WHITE
7	TCGA-E2-A15K	TCGA-A2-A0CQ	BLACK		TCGA-E2-A15K	WHITE
8	TCGA-E2-A56Z	TCGA-A2-A1G4	WHITE		TCGA-E2-A56Z	BLACK
9	TCGA-E2-A1LB	TCGA-A2-A25A	WHITE		TCGA-E2-A1LB	BLACK
10	TCGA-E2-A15O	TCGA-A7-A0CD	WHITE		TCGA-E2-A15O	BLACK
11	TCGA-E2-A152	TCGA-A7-A13G	WHITE		TCGA-E2-A152	WHITE
12	TCGA-D8-A1XC	TCGA-A7-A26E	WHITE		TCGA-D8-A1XC	WHITE
13	TCGA-E9-A295	TCGA-A7-A26F	WHITE		TCGA-E9-A295	WHITE
14	TCGA-D8-A1JF	TCGA-A7-A26H	WHITE		TCGA-D8-A1JF	WHITE

E2=INDEX(B:B,MATCH($A2,$B:$B,0))
copied down and across

[Marwah]

I guess what I need is columns E and F
how did you find them ??

[Marwah]

E2=INDEX(B:B,MATCH($A2,$B:$B,0)) doesnt give me F column in your post but it repeats column A

[Marwah]

please tell me how did you find column F

[Marwah]

@avk could you please redo the match according to the file I uploaded please

[avk]

Dear Marwah, File attach for your ready ref. using index match with criteria column "I"

[FDibbins]

E2=INDEX(B:B,MATCH($A2,$B:$B,0)) doesnt give me F column in your post but it repeats column A

So show us in your file, what you want. that way we can all stop guessing

[Marwah]

@FDibbins found column F which I would like to get.

@avk can you help with that ?

[FDibbins]

@FDibbins found column F which I would like to get.

@avk can you help with that ?

Show us in your file, what you want

[Marwah]

I want to get column F that you created

[FDibbins]

Ok, I have asked twice for you to provide a sample file showing what your expected outcome would look like, but you seem not to want to do that, so perhaps another member will be able to better guess what you want.

Best of luck

[Marwah]

I will say it in a better way
so the first value in Column A2 is TCGA-A2-A3XY
then I search for this value in column B the I found it ,, I look and the race next to it in C then it is Black and create the following


TCGA-A2-A3XY black
TCGA-XX-A89A white

and so one

[Marwah]

@FDibbins I just finished doing the file sorry for being late

[Marwah]

@avk
I will say it in a better way
so the first value in Column A2 is TCGA-A2-A3XY
then I search for this value in column B the I found it ,, I look and the race next to it in C then it is Black and create the following


TCGA-A2-A3XY black
TCGA-XX-A89A white

[avk]

Did you mean result as per column "C". Search in column "B" based criteria on column "A" . If this is correct, plz refer attach file.

[Marwah]

Thank you, yes that is what i need