Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-877403%22%20slang%3D%22en-US%22%3EIs%20there%20a%20formula%20to%20drag%20data%20listed%20in%20rows%20into%20a%20single%20column%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-877403%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22lia-note-subject%20lia-component-subject%22%3E%3CDIV%20class%3D%22NoteListSubjectCell%20message-subject%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fnotes%2Fprivatenotespage%2Ftab%2Foutbox%2Fnotes-view-mode%2Fsingle%2Fnote-id%2F125134%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3EIs%20there%20a%20formula%20to%20drag%20data%20listed%20in%20rows%20into%20a%20single%20column%3F%20I%20would%20rather%20not%20copy%20and%20transpose%20paste%20all%202304%20rates.%3C%2FA%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22lia-note-body%20lia-component-body%22%3E%3CP%3EI%20have%20a%20matrix%20of%20state%20to%20state%20rates%20in%20the%20contract-state...tab.%3C%2FP%3E%3CP%3EThe%20origin%20state%20is%20in%20ColumnA%20and%20the%20and%20the%20Destination%20state%20is%20in%20Row1%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20Sheet1%20I%20have%20the%20ST-ST%20set%20column%20A.%20I%20would%20like%20to%20have%20the%20rates%20populate%20in%20column%20B.%20to%20make%20vlookup%20easier.%3C%2FP%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-877403%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20formulas%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-877978%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20there%20a%20formula%20to%20drag%20data%20listed%20in%20rows%20into%20a%20single%20column%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-877978%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F381691%22%20target%3D%22_blank%22%3E%40ESKARDA%3C%2FA%3E%26nbsp%3BAre%20you%20sure%20you%20attached%20the%20correct%20file%3F%20It's%20a%20CSV%20file%20and%20there%20is%20no%20Sheet1%2C%20just%20two%20columns%20of%20data.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20exactly%20is%20your%20question%3F%20Do%20you%20want%20to%20know%20how%20to%20do%20a%20Vlookup%3F%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20help%20us%20understand%20what%20you%20want%20to%20achieve.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-879176%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20there%20a%20formula%20to%20drag%20data%20listed%20in%20rows%20into%20a%20single%20column%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-879176%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20apologies.%20I%20attached%20the%20correct%20spreadsheet.%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20Contract-State-R%20tab%20is%20a%20state%20to%20state%20rate%20matrix.%20The%20Origin%20states%20are%20in%20Column%20A%20and%20the%20destination%20states%20are%20on%20row%202.%20I%20am%20looking%20for%20the%20most%20efficient%20way%20to%20get%20the%20rates%26nbsp%3Binto%20Column%20B%20(next%20to%20the%20corresponding%20ST-ST%20in%20Column%20A)%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-879972%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20there%20a%20formula%20to%20drag%20data%20listed%20in%20rows%20into%20a%20single%20column%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-879972%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F381691%22%20target%3D%22_blank%22%3E%40ESKARDA%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20I%20understood%20your%20logic%20correctly%3C%2FP%3E%0A%3CP%3EFor%20such%20source%20matrix%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20664px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133797i6E6EA27A3FCDB704%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Econvert%20it%20to%20two%20columns%20(J%20and%20K)%20could%20be%20used%3C%2FP%3E%0A%3CP%3Ein%20J3%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DINDEX(%24B%3A%24B%2C%20INT(%20(ROW()-ROW(%24B%242)-1)%2F%24J%241)%2BROW(%24B%242)%2B1)%20%26amp%3B%0A%20%20%20%22-%22%20%26amp%3B%0A%20%20%20INDEX(%24C%242%3A%24H%242%2CMOD(ROW()-ROW(%24B%242)-1%2C%24J%241)%2B1)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ein%20K3%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DINDEX(%0A%20%20%20INDEX(C%3AH%2C%0A%20%20%20%20%20%20INT(%20(ROW()-ROW(%24B%242)-1)%2F%24J%241)%2B%0A%20%20%20%20%20%20ROW(%24B%242)%2B1%2C0%0A%20%20%20)%2C%0A%20%20%20MOD(ROW()-ROW(%24B%242)-1%2C%24J%241)%2B1%0A)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20need%20to%20be%20adjusted%20to%20your%20actual%20ranges%20and%20sheets.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-888844%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20there%20a%20formula%20to%20drag%20data%20listed%20in%20rows%20into%20a%20single%20column%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-888844%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20are%20a%20life%20saver%20thank%20you%20so%20much%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-888863%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20there%20a%20formula%20to%20drag%20data%20listed%20in%20rows%20into%20a%20single%20column%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-888863%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F381691%22%20target%3D%22_blank%22%3E%40ESKARDA%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
ESKARDA
Occasional Contributor

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.

5 Replies

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

@Ingeborg Hawighorst 

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) 

@ESKARDA 

If I understood your logic correctly

For such source matrix

image.png

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.

You are a life saver thank you so much@Sergei Baklan 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies