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?
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.
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.
Use AutoFilter,
select the values you’re after
Use select visible
Select : Cut : Paste
Sort both sheets to remove blank rows
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.