Excel Forum

Loading / identifing a ranges of rows

Mark Mahoney

New Member

Members

Forum Posts: 1

Member Since:
April 30, 2021

Offline

April 30, 2021 - 4:54 am

I have data that I want to ensure that specific ranges of rows remain in the dataset.

In the example below. I want to keep 4 rows below item c in col1. For every entry of 'c'.

Col 3 shows for example purposes the rows that I want to remain.

I cannot for the life of me find a way of doing it.

Col 1	Col 2	Col 3
a	1
b	2
c	3	x
x	4	x
y	5	x
fff	6	x
g	7
h	8
i	9
j	10
k	11
l	12
c	13	x
d	14	x
e	15	x
f	16	x
g	17
r	18
s	19
c	20	x
cd	21	x
er	22	x
ff	23	x

Mynda Treacy

Admin

Forum Posts: 4515

Member Since:
July 16, 2010

Offline

April 30, 2021 - 7:19 pm

Hi Mark,

Welcome to our forum!

You can use this formula in a new column to extract the values from column 1:

= try if [Col 1] = "c" then [Col 1] else
if #"Changed Type"{[Col 2]-2} [Col 1] = "c" then #"Changed Type"{[Col 2]-1} [#"Col 1"] else 
if #"Changed Type"{[Col 2]-3} [Col 1] = "c" then #"Changed Type"{[Col 2]-1} [#"Col 1"] else 
if #"Changed Type"{[Col 2]-4} [Col 1] = "c" then #"Changed Type"{[Col 2]-1} [#"Col 1"] else null otherwise null

Then filter out the null rows. See file attached.

Mynda

SunnyKow

Puchong, Malaysia

VIP

Members

Trusted Members

Forum Posts: 1432

Member Since:
June 25, 2016

Offline

April 30, 2021 - 10:19 pm

Hi Mark

You could also use a formula instead.

In cell C2 enter =IF(A2="c",1,IF(C1<>0,IF(C1<>4,C1+1,0),0))

You can then filter out the zeros.

Sunny

Forum Timezone: Australia/Brisbane

Most Users Ever Online: 245

Currently Online: Catalin Bombea, John Kobiela, Lia Ro, Malcolm Sawyer

Guest(s) 10

Currently Browsing this Page:
1 Guest(s)

Top Posters:

SunnyKow: 1432

Anders Sehlstedt: 873

Purfleet: 414

Frans Visser: 346

David_Ng: 306

lea cohen: 222

Jessica Stewart: 216

A.Maurizio: 202

Aye Mu: 201

jaryszek: 183

Newest Members:

Melanie Ford

Isaac Felbah

Adele Glover

Hitesh Asrani

Rohan Abraham

Anthony van Riessen

Erlinda Eloriaga

Abisola Ogundele

MARTYN STERRY

Rahim Lakhani

Forum Stats:

Groups: 3

Forums: 24

Topics: 6356

Posts: 27793

Member Stats:

Guest Posters: 49

Members: 32324

Moderators: 3

Admins: 4

Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT

Moderators: MOTH Support, Velouria, Riny van Eekelen

Loading / identifing a ranges of rows |Power Query|Excel Forum|My Online Training Hub