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...tab.

The origin state is in ColumnA and the and the Destination state is in Row1

 

On Sheet1 I have the ST-ST set column A. I would like to have the rates populate in column B. to make vlookup easier.

Excel formulas

5 Replies

  • 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.

Resources