• 464K Members
• 11.9K Online
• 561K Conversations

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

Occasional Contributor

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

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

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

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

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

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)

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

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
)``````

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

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

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

@ESKARDA , you are welcome

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies