# Need help re-organizing an Excel spreadsheet

**URL:** <https://boards.straightdope.com/t/need-help-re-organizing-an-excel-spreadsheet/569886>\
**Category:** Factual Questions\
**Created:** [February 2, 2011, 1:36pm UTC](https://boards.straightdope.com/t/need-help-re-organizing-an-excel-spreadsheet/569886 "2011-02-02T13:36:49Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Random\_Design](https://avatars.discourse-cdn.com/v4/letter/r/58f4c7/32.png) [@Random\_Design](https://boards.straightdope.com/u/Random_Design)\
**Post date:** [February 2, 2011, 1:36pm UTC](https://boards.straightdope.com/t/need-help-re-organizing-an-excel-spreadsheet/569886/1 "2011-02-02T13:36:49Z")

</div>

Sorry I know questions like this are tedious.  
I’ve got a spreadsheet with a bunch of info on the first (and so far only) worksheet. What I’d like to do is where a certain column value is X, move that entire row into the X worksheet. Are there functions to do this?

---

<div class="post-metadata">

**Author:** ![Dave\_Hartwick](https://avatars.discourse-cdn.com/v4/letter/d/7feea3/32.png) [@Dave\_Hartwick](https://boards.straightdope.com/u/Dave_Hartwick)\
**Post date:** [February 2, 2011, 1:52pm UTC](https://boards.straightdope.com/t/need-help-re-organizing-an-excel-spreadsheet/569886/2 "2011-02-02T13:52:16Z")

</div>

OFFSET and MATCH could do, if you want a function. Say the values for the column headers are “Sales”, “Tax”, and “Region” and they’re in the range Sheet1 B1:Z1.

On your target sheet put the values in B1, C1, and D1.

B2 formula =OFFSET(Sheet1!$A1,ROW(B2),MATCH(B$1,Sheet1$B$1:$Z$1,0))

Should return the value one row below the cell on Sheet1 where the value “Sales” appears. Copy the formulas through the range you need, say Sheet2 B2:D250. I haven’t tested this and I’m a little uncertain if you can use MATCH in a horizontal array like that. Maybe you could transpose the array to calculate the number of columns to offset.

---

<div class="post-metadata">

**Author:** ![FasterThanMeerkats](https://avatars.discourse-cdn.com/v4/letter/f/278dde/32.png) [@FasterThanMeerkats](https://boards.straightdope.com/u/FasterThanMeerkats)\
**Post date:** [February 2, 2011, 3:44pm UTC](https://boards.straightdope.com/t/need-help-re-organizing-an-excel-spreadsheet/569886/3 "2011-02-02T15:44:48Z")

</div>

Can you provide more information on how the data is organized? Is it a large block? Is it values, or does it have formulas? Do you need formulas intact or are values ok?

If your data is in a block (headings at top, records in rows) and you just need to copy/paste, you can:

-Sort the data by the column that contains your X values

- 

```
  this will bunch all the records with X together

```

-Copy the block of records that contain X onto a new sheet

Saves you from hunting/pecking individual records to copy/paste. If you try using formulas, like Offset and Match, you certainly can, but it’ll probably end in a nightmare of formulas.

---

<div class="post-metadata">

**Author:** ![keno](https://avatars.discourse-cdn.com/v4/letter/k/f9ae1b/32.png) [@keno](https://boards.straightdope.com/u/keno)\
**Post date:** [February 2, 2011, 4:16pm UTC](https://boards.straightdope.com/t/need-help-re-organizing-an-excel-spreadsheet/569886/4 "2011-02-02T16:16:25Z")

</div>

Use AutoFilter,  
select the values you’re after  
Use select visible  
Select : Cut : Paste  
Sort both sheets to remove blank rows

---

<div class="post-metadata">

**Author:** ![Random\_Design](https://avatars.discourse-cdn.com/v4/letter/r/58f4c7/32.png) [@Random\_Design](https://boards.straightdope.com/u/Random_Design)\
**Post date:** [February 3, 2011, 9:49am UTC](https://boards.straightdope.com/t/need-help-re-organizing-an-excel-spreadsheet/569886/5 "2011-02-03T09:49:38Z")

</div>

Thanks guys. Lol, c&p… sometimes the simplest option is just invisible, don’t know why I got into my head that I had to use a function.

---

<div class="post-metadata">

**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\
**Post date:** [February 3, 2011, 12:59pm UTC](https://boards.straightdope.com/t/need-help-re-organizing-an-excel-spreadsheet/569886/6 "2011-02-03T12:59:10Z")

</div>

> [@Random\_Design](#):
>
> Thanks guys. Lol, c&p… sometimes the simplest option is just invisible, don’t know why I got into my head that I had to use a function.

Depends on if this is a one-time change you want to make, or you want it to be more automatic as you add more data. If one-time, then I agree that sorting/filtering/C&P is the way to go.