Forum Discussion

ESKARDA's avatar
ESKARDA
Copper Contributor
Sep 26, 2019

Is there a formula to drag data listed in rows into a single column?

Is there a formula to drag data listed in rows into a single column? I would rather not copy and transpose paste all 2304 rates. I have a matrix of state to state rates in the contract-state...ta...
Excel formulas
IngeborgHawighorst's avatar
IngeborgHawighorst
MVP
Sep 26, 2019

ESKARDA Are you sure you attached the correct file? It's a CSV file and there is no Sheet1, just two columns of data.

 

What exactly is your question? Do you want to know how to do a Vlookup? 

 

Please help us understand what you want to achieve.

ESKARDA's avatar
ESKARDA
Copper Contributor
Sep 27, 2019

IngeborgHawighorst 

My apologies. I attached the correct spreadsheet.   

In the Contract-State-R tab is a state to state rate matrix. The Origin states are in Column A and the destination states are on row 2. I am looking for the most efficient way to get the rates into Column B (next to the corresponding ST-ST in Column A) 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 27, 2019

    ESKARDA 

    If I understood your logic correctly

    For such source matrix

    convert it to two columns (J and K) could be used

    in J3

    =INDEX($B:$B, INT( (ROW()-ROW($B$2)-1)/$J$1)+ROW($B$2)+1) &
   "-" &
   INDEX($C$2:$H$2,MOD(ROW()-ROW($B$2)-1,$J$1)+1)

    in K3

    =INDEX(
   INDEX(C:H,
      INT( (ROW()-ROW($B$2)-1)/$J$1)+
      ROW($B$2)+1,0
   ),
   MOD(ROW()-ROW($B$2)-1,$J$1)+1
)

    and need to be adjusted to your actual ranges and sheets.

    • ESKARDA's avatar
      ESKARDA
      Copper Contributor
      Oct 02, 2019

      You are a life saver thank you so muchSergeiBaklan 

Resources