maintain row order INDEX - DISTINCT

I'm pulling index/distinct from one sheet, and the same from another sheet. Then index/distinct/collect to have the master distinct list from both sheets. In another column, each distinct entry in the master column is assigned a number (1-5). This number is then pulled back into the original sheets for hierarchy reference.

Problem: if another row is added to either original sheet, somewhere in the middle (where it might have to be), then this new value is placed around the middle of the master column, throwing off the assigned reference numbers for everything below it.

Is there any way around this, to maintain the row order in the master column? It's too long for a nested IF.

Find more posts tagged with

Accepted answers

MWJ

@jjg279

Thanks for the reply, but I'm not sure what that would accomplish.

In the event there is no way to maintain the row order of index/distinct/collect when there may be entries made in the original column, I made another copy/paste column of the master, then another to do a vlookup between the two, with cond format should there not be a match (at which time I can add the entry to the bottom of the copy/pasted column and maintain row order).

Still looking for a better solution than mine, though.

All comments

jjg279

Howdy! I had a similar issues for a sheet we were working on. This was the solution we came up with, not sure if it will work for you. We created a column called "Count" and made it a formula column that just equaled 1. Then made an "Index" column with the formula "=COUNT(Count$1:Count@row)".

Not sure that actually answers your question, but the index column is dynamic.

MWJ

@jjg279

Thanks for the reply, but I'm not sure what that would accomplish.

Still looking for a better solution than mine, though.

jjg279

Are you using the "autonumber" column type for your index? That will continue to provide the next unique number regardless of where the row is entered, and if you want to keep your two sheets distinct you could start one sheet at a much larger number.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.