►
Description
This video shows how to moves certain rows that meet a criteria from one Excel table (source) into a another table (target) on another worksheet using (1) plain values based selection and (2) using column filter all using Office Scripts.
Source code, input Excel files and much more at: https://github.com/sumurthy/officescripts-projects/tree/main/Move%20Rows%20Across%20Tables
A
Hello
in
this
example,
I'm
going
to
show
how
to
move
rows
between
one
table
to
another
and
also
manage
the
filters
that
may
exist
on
the
source
table.
So
here
I
have
two
worksheets
and
each
of
these
worksheets
contain
the
same
table,
the
same
structure,
so
let's
call
the
table
and
sheet
one
as
my
target
table.
So
this
is
where
I
want
to
move
the
rows
from
the
second
table.
That's
over
at
g2.
A
Now,
as
you
can
see
here,
there
are
a
set
of
products
and
categories
and
sale
and
other
information
over
here
on
my
target
table.
I
don't
have
clothing
and
bikes
in
terms
of
the
category,
so
here
I
have
clothing
and
bikes.
So
let's
imagine
that
I
want
to
bring
over
all
the
clothing
rows
over
to
the
target
table
so
from
source
table
all
the
way
to
the
target
table
by
just
selecting
the
rows
that
where
the
category
is
clothing
now
the
source
table
that's
over
in
sheet.
A
A
If
you
already
have
filters,
so
let's
go
ahead
and
apply
them
and
see
how
to
handle
this.
So
let's
say
I'm
looking
for
all
products
greater
than
2015,
so
let's
say
and
then
I'm
going
to
apply
cat
filter
on
category
to
include
just
say,
bikes
and
clothing
and
then
so,
let's,
let's
also
apply
some
filters
on
the
product.
A
A
A
So
to
do
this,
I'm
going
to
use
the
the
office
scripts,
and
so
this
is
excel
online.
So
I'm
going
to
go
over
to
automate
tab
and
open
the
code
editor
for
the
first
script.
I'm
going
to
do
this
using
just
the
range
values,
I'm
not
going
to
use
the
the
filter
technique
I'll
I'll!
Do
that
towards
the
end
of
the
video
using
another
script.
A
A
Let's
pick
the
target
and
the
source
table
names,
so
let's
see
I'm
going
to
go
over
here
and
find
out
the
table
name.
So
this
is
table
one.
So
I'm
going
to
call
it
table
one.
So
this
could
be
anything
that
you
have
in
your
workbook,
so
this.
For
some
reason
the
tab
is
not
showing
up
the
design
type.
So
let's
call
it
table
two.
A
So
I
have
two
variable
names
and
now
I'm
gonna
need
to
assign
the
the
index
of
the
column
where
I
want
to
go
and
do
a
lookup.
So
here
it's
zero
index,
so
it's
zero
and
one
so
category
is
in
in
index
one.
So
this
is
index
of
column
to
do
a
lookup
on
or
do
a
filter
on,
not
the
table
filter
but
just
a
lookup
filter.
A
A
A
A
A
A
A
A
A
I'm
going
to
run
again
all
right,
so
you'll
see
that
I
have
I've
captured
the
actual
existing
existing
filters.
So,
as
you
can
see
in
this
source
table,
I
have
filters
on
year.
Category
and
product
sales
and
ratings,
don't
have
don't
have
any
filters
and
then
I
can
expand
on
this
to
see
what
kind
of
filters
are
applied.
A
So,
in
the
case
of
a
year
it's
greater
than
2015
category
of
picked
bikes
and
clothing
and
then
for
products
of
I
have
an
array
of
values
that
includes
you
know
the
shorts,
the
bikes
and
the
socks
right
so
and
you'll
also
see
that
there's
this
additional
key
or
data
type,
which
is
not
really
required.
When
I
want
to
apply
back
so
I'm
going
to
filter
those
out,
I'm
going
to
remove
those
keys.
A
Now
before
I
save
it
in
the
object,
I'm
gonna
I'm
gonna
check
well
I'll.
Rather
once
I'm
gonna
check,
I
don't
wanna
capture
the
criteria
if
there
is
no
filter
on
it.
So
I'm
gonna
check
hey
if
that
object
exists.
That
means,
if
it's
not
a
null
to
you
know
only
then
save
it.
Otherwise
I
can
just
discard
it.
I
don't
need
it
all
right
so
now
that
I
have
done
that,
I'm
gonna
remove
right
before
saving
I'm
gonna
remove
a
key.
A
So
now,
when
I
run
the
same
thing,
I
should
only
see
three
entries
in
the
object.
The
sales
and
ratings
shouldn't
exist
and
also
I
don't
want
to
see
the
subfield
or
the
odata
keys,
so
effects
here.
As
you
can
see,
this
is
only
three
and
then
I
don't
see
the
subfield
or
the
odata
type
all
right.
So
I
think
I've
captured
all
the
filters
as
it
exists
on
the
source
table.
Now
I
can
move
on
to
the
kind
of
the
core
part
of
the
logic.
A
A
Okay,
so
this
gives
me
all
the
data
values
associated
with
this,
so
you
know
regard
so.
This
happens
regardless
of
what
filters
exist.
So
this
is
going
to
get
the
underlying
values
of
this
table.
So
I've
already
applied
many
filters
here,
so
it
doesn't
matter
so
it's
going
to
go
and
get
ignore
those
filters
and
get
the
values
now.
A
So
obviously
I
don't
want
to
select
all
the
rows.
I
only
want
to
select
the
clothing
rows
and
the
act
of
sort
of
moving
would
involve
me.
One
number
one
gather
all
the
rows
insert
into
the
target
table
which
is
over
at
sheet1
and
then
come
back
and
remove
these
rows.
So
to
do
that
first,
I
need
to
create
sort
of
two
more
variables.
One
is
capturing
rows
to
move
and
then
second
is
to
capture
their
addresses,
so
I
can
come
back
and
remove
them.
A
A
Now
some
I'll
go
and
define
the
types
here.
So
what
this
one
is
either
a
number
or
a
string
or
a
boolean,
and
it's
a
two-dimensional
array,
whereas
the
row
address
to
remove
is
just
the
string
right
all
right.
So
now
I'm
going
to
go
over
each
of
the
rows
and
apply
my
criteria,
which
is
to
select
only
the
clothing
rows
and
then,
if
it
matches
that
I'm
going
to
collect
those
rows
and
also
I'll
capture
the
address.
So
for
that
I'll
just
create
a
simple
loop.
A
A
A
A
A
So
on
that
row
I'll
get
the
first
cell
so
which
case,
let's
say
I'm
in
this
row
here
on
954.,
it's
going
to
get
the
intersection
between
the
entire
table,
however
big.
That
is
so
that
entire
range
and
then
I'm
going
to
say,
hey
for
this
entire
rows
to
get
the
entire
row
first,
I
need
to
get
the
cell,
the
very
first
cell,
because
that's
the
only
one
I
know
of
and
then
I
want
to
select
the
entire
row
so
connect
the
intersection
between.
A
A
And
now
I'm
gonna
capture
the
row
address
to
remove
I'll
push
the
address
into
that,
so
all
right,
so
that
was
kind
of
involved,
a
set
of
logic
here,
so
to
make
sure
that
I'm
getting
the
right
values
I'm
going
to
display
both
of
these
in
the
console
before
I
move
on.
So
this
is
just
a
checkpoint
to
make
sure
that
I'm
capturing
the
right
rows
and
I'm
capturing
capturing
the
right
address
for
for
it
to
be
removed
later
on.
A
A
All
right,
so
I'm
getting
five
rows
that
looks
about
right.
So
for
each
of
the
rows
I
should
see
the
year
category,
product
sales
and
rating.
So
that's
correct,
so
you'll
see
that
two
of
them
appear
in
the
filter
as
well.
So
that's
good
and
then
I
expect
to
see
the
same.
Five
range
address
values.
So
that's
that's
good,
so
I
think
so
far.
It's
kind
of
working
as
I
expected
now
that
I
have
the
rows
to
move
and
then
I
have
the
address
to
be
removed
later.
A
I
want
to
apply
some
checks
before
I
further
move
on,
because
you
know
right
now,
I'm
getting
the
rows
that
I
that
I
expect
to
see.
But
that
may
not
always
be
the
case.
So
I
want
to
make
sure
that
before
I
proceed
further,
I
do
some
checks.
So
I'll
say:
hey
you
know.
If
the
rows
to
move
length
is
less
than
one,
which
means
I
don't
have
a
single
row
to
move
I'll,
say:
hey,
console.log,
no
rows
selected
and
then
I'll
just
return,
which
means
I'll
end
the
script.
A
I
don't
want
to
go
any
further,
all
right
so
now
that
I've
collected
the
rows,
I'm
going
to
go
ahead
and
add
them
into
the
target
table,
so
I'll,
say,
target
table,
add
rows
and
I'll
add
it
at
the
end.
So
that's
-1,
which
is
the
first
argument
and
then
I'll
send
in
the
the
two-dimensional
array
values.
A
So
now
the
addition
part
is
done.
So
the
next
part
is
to
actually
go
and
remove
these
rows.
So
to
remove
that,
I
first
need
to
get
the
reference
to
the
sheet
where
these,
where
this
table
exists,
because
I
have
to
delete
the
range
and
to
get
the
to
to
get
the
range.
I
need
to
get
the
get
it
off
of
the
sheet.
So
give
me
this
from
the
source
table.
A
Get
me
the
worksheet
right
so
now
that
I
have
the
sheet
reference
before
I
remove,
I
do
have
to
remove
all
the
all
the
filters
that
are
on
this
table,
because
if
I
try
to
remove
a
row,
that's
that's
behind
a
filter.
That's
not
being
shown
excel,
doesn't
like
it.
So
what
I'll
do
first
is
on
the
source
table
I'll,
remove
I'll
get
all
the
auto
filter.
A
So
this
is
at
the
table
level,
the
worksheet
level
I'm
going
to
clear
all
the
criteria.
So
what
it
does
is
it'll
remove
all
of
the
existing
filters,
and
now
it's
clear
for
me
to
go
and
and
remove
all
the
rows
now.
Remember.
I've
already
saved
all
the
existing
filters,
so
I'm
not
afraid
to
remove
it,
because
I
can
always
apply
back
now
so
now
I'll
go
through
each
of
the
row
address
to
remove-
and
I
will
reverse
that
array.
A
So
the
row
address
to
remove
is
is
just
an
array,
so
I'm
going
to
reverse
it
because
I
want
to
remove
starting
from
the
bottom.
So
this
is
really
important.
If
you
don't
do
this,
you
will
end
up
removing
the
wrong
rows
and
towards
the
end,
it
will
eventually
go.
You
know
outside
of
the
table
range
and
it
will
actually
give
an
error.
So
it
will
not
only
create
data
issues,
but
also
it
will
end
up
getting
an
error.
A
Okay,
so
with
this
now
I
will
go
ahead
and
remove
you
know
I'll
actually
try
to
apply
the
filters
again
before
I
run
it
so
so
far
what
I've
done
is
I've
collected
the
rows.
I've
collected
the
address.
I
got
the
reference
to
the
sheet.
I
cleared
all
the
the
existing
filter
criteria,
I'm
removing
all
the
rows
now
that
I've
that's
been
added
to
the
the
target
table.
A
So
before
I
run
this,
I
will
apply
the
filter
again.
So
what
I'll
do
is
I'll
say
for
each
of
the
table
filter
that
I've
collected
all
the
way
here.
So
this
object
has
keys
as
column
names
and
the
value
is
the
actual
filter
criteria.
So
I
need
that
so
to
do
that.
To
you
know
traverse
through
each
of
the
keys,
I
will
use
the
the
typescript
object,
keys
method
and
I
will
supply
the
table
filters
to
it.
So
this
will
return
me
an
array
of
each
of
the
column
names
where
the
filter
exists.
A
A
So
what
I'm
going
to
do
is
on
the
column,
where
I'm
doing
this
whole
lookup,
which
is
in
the
category
column
I'm
going
to
you
know
just
basically
clear
all
the
filters.
So
that
way,
it
may
not
be
exactly
as
I
found
it,
but
at
least
the
user
will
be
able
to
see
some
rows
if
it's
okay
for
you
to
you,
know,
remove
all
the
or
reapply
all
the
filters
and
not
have
any
rows
show
up.
So
then
you
don't
have
to
do
this,
but
in
my
case
what
I'll
do
is.
A
A
A
A
There
is
another
way
to
do
this,
which
is
you
know
if
you
think
back
at
the
source
table.
So
instead
of
doing
look
up
on
the
values,
you
could
say
that
I'm
going
to
clear
all
the
the
existing
filters.
So
in
this
case,
let's
say,
if
you
clear
all
the
filters
you're
going
to
get
a
bunch
of
rows
now,
you
could
then
apply
filter
only
on.
A
Filter
just
on
clothing
and
then
move
these
throws
over
and
then
come
back
and
reapply
the
the
filters
as
as
it
existed
right
so
so
the
key
difference
is
instead
of
collecting
all
the
rows
by
looking
up
on
the
range
values
I
can
then,
and
I
can
use
just
the
the
filter
on
the
table
to
collect
all
the
rows.
So
I
think
I
should
be
able
to
you
know,
reuse,
most
of
the
logic
that
I
have
so
I'm
going
to
save
this
as
another
script.
A
A
A
So,
in
this
case,
I'll
go
ahead
and
do
the
apply
values
filter.
So
this
accepts
an
array
and
in
my
case
I
only
have
one
value
to
filter
on.
If
you
have
many,
you
know
you
can
include,
say
bikes
or
whatever
else
that
you
want
to
filter
on
so
so
this
is
a
very
flexible
apis.
You
can
add
as
many
values
as
you
want,
given
that
it
accepts
an
array
all
right.
A
So
now
that
I've
cleared
the
filter,
I
applied
a
new
filter
based
on
the
value
that
I
want
to
filter
on
now
I
want
to
go
and
get
the
actual
range
that
is
visible
once
that
new
filter
gets
applied
so
on
the
source
table.
I
get
the
range
I'll
get
the
the
visible
view.
So
this
gives
me
the
visible
range,
and
now
I
will.
A
A
A
A
A
The
top
row,
so
it
gives
a
two-dimensional
array
now
that
I
know
that
this
is
only
one
row
at
a
time
I'm
going
to
just
collect
the
first
row.
So
the
end
of
this,
I
should
have
the
data
rows
populated
with
all
the
values
of
what
I've
selected
here
in
this
case
right
all
right,
so
I
will,
as
before,
I'm
gonna
go
ahead
and
add
to
the
the
table.
So
before
I
add,
I
will
have
to
remove
the
header
row,
so
it's
easily
done
with.
A
A
A
A
A
I'm
collect
I'm
going
to
collect
the
data
values
which
I
will
then
use
to
insert
I'll.
Remove
the
header
row
before
I
do
that
and
then
I'm
going
to
then
collect
all
the
range
addresses
and
again
I'll,
remove
the
top
row,
which
is
the
header
and
then
I'll,
get
the
sheet
and
then
I'll
clear
the
criteria,
all
the
of
the
filter
and
then
I'm
going
to
reverse
that
address
row
like
before,
delete
the
rows
from
the
bottom
and
then
reapply
all
the
filters,
except
for
the
column
where
we
did
the
lookup
on
all
right.
A
So
with
this
I
should
expect
to
see
the
same
result.
So
before
I
do
the
that
I
will
reapply
the
filters
that
I
had
originally,
which
is
bikes
and
clothing,
and
then
I
will
apply
the
filter
on
the
year,
let's
say
greater
than
2015
here
and
then
I'll
also
pick
some
some
product,
some
product
categories
here,
maybe
a
couple
of
them
all
right.
So
I
see
both
clothing
and
bikes
right
and-
and
I
expect
to
see
all
the
clothing
rows
to
be
appended.