Move Cell Value in Row 2 to Match Location in Row 1

  • Thread starter Thread starter i_know_it_can_be_done
  • Start date Start date
I

i_know_it_can_be_done

I have a row of ascending column values, 1-20.
I have another row with values only in 3 columns.

say, 2, 5, and 12.

How can I move/sort the data in the second row such that
it is NOT under columns 1, 2, and 3, but lines up under
the associated value/column in the top row???

In other words, so that 2 is under column 2, 5 is under
column 5 and so on...

I must do this with very many rows, so looking for
automation/macro/etc. ANY HELP WOULD BE GREATLY
APPRECIATED. Thanks,

Timothy Leary
 
Give this a try ..

Assuming your source data is in Sheet1, row2 down
with row1 containing the numbers 1 - 20 in A1:T1

In a new Sheet2
-------------------
Number 1 - 20 across cols A to T in row1

Select A2:T2

Put in the *formula bar*:

=IF(Sheet1!A2=$A$1:$T$1,Sheet1!A2,IF(Sheet1!
B2=$A$1:$T$1,Sheet1!B2,IF(Sheet1!C2=$A$1:$T$1,Sheet1!
C2,"")))

Now *array-enter* the formula, viz.:
Hold down CTRL & SHIFT and press ENTER
(instead of just pressing ENTER)

Excel will wrap curly braces {} around the formula
(Do not type-in these curly braces yourself)

The same formula will appear in every cell
in the range A2:T2

With A2:T2 selected, copy down
as many rows as you have data in Sheet1

The above should achieve what you're after
 
Thank you,

I think I'm on my way to figuring it out.

Actually, my source data is 6 fields in Sheet2 (B through
G), row 2 down.

Sheet 1 (columns B - U), row 1 has the ascending value
list (1-20)and row 2 down is where I am placing the
formulas that you suggested, and having some success. I
think I need to expand your formula out to 6 "IF's"
because of what I mentioned above. CTRL-SHIFT-ENTER worked
across, but a bit of trouble "copying down" - fill handle
was not incrementing the B2's to B3's and B4's for example.

I also had trouble changing the view from the formula to
value for multiple cells. It seems I can only do each cell
at a time.

Sorry not much experience with Excel...

Anyway, thanks again for your help!

TL
 
Ok, with the specifics given, try the revised steps
& revised / extended formula below:

In Sheet1
---------
Select B2:U2

Put in the *formula bar*:

=IF(Sheet2!B2=$B$1:$U$1,Sheet2!B2,IF(Sheet2!
C2=$B$1:$U$1,Sheet2!C2,IF(Sheet2!D2=$B$1:$U$1,Sheet2!D2,IF
(Sheet2!E2=$B$1:$U$1,Sheet2!E2,IF(Sheet2!
F2=$B$1:$U$1,Sheet2!F2,IF(Sheet2!G2=$B$1:$U$1,Sheet2!
G2,""))))))

Array-enter the formula,
viz.: CTRL + SHIFT + ENTER (as before)

This will enter the formula into B2:U2
(which does it for the numbers in row2)

Ensure B2:U2 is selected

Drag the fill handle at
the lower right-corner* of U2
down as many rows as you have source data in Sheet2

*point the cursor at this corner,
it will turn into a black cross

The array formula *should* fill
correctly as you drag down

--

To remove the array formulas,
i.e. freeze/convert to values

Just do an *in-place*
Copy > paste special > check "values" > OK
to overwrite all the formulas with values

Try this:

Select carefully the entire range of the formulas
e.g. select B2:U1000

Right-click on the selection > Copy

Right-click again on the selection (a 2nd time)
Choose Paste special > Check "values" under Paste > OK

That should do it

Drop a line or two here on how it turned out ..
 
I don't know why this did not post before, but I tried to
say (prior to your email)...

THANK YOU!!!!!!!!

It works like a charm - everything filled in correctly
(when I adjusted it for my particular data).

YOU WERE VERY HELPFUL. I could not find the solution in
any FAQ's, help menus, or knowledge base. I cannot thank
you enough for responding!!!

tis done...
 
Back
Top