+ Reply to Thread
Results 1 to 11 of 11

Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"

  1. #1
    Registered User
    Join Date
    04-05-2017
    Location
    Floida, USA
    MS-Off Ver
    2013
    Posts
    4

    Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"

    Hello,

    First post (aside from intro) and I believe I have found the correct place after reviewing other posts.

    I am attempting to restructure a list of data (a cross-reference) - I have included a small sample set here.
    My sheet has pages in Col A and B-FL are design requirements shown on the pages. Many pages do not have requirements in every cell to FL column

    Col A occupies 329 rows

    Sample Data in Sheet1
    Page Req Req Req Req
    63 4 55
    64 9 5 7
    65 8 6 16 9
    66 53 17 5
    67 67 556 75


    My desired results follow
    Desired Results on sheet2:
    REQ Page
    4 63
    5 64
    5 66
    6 65
    7 64
    8 65
    9 64
    9 65
    16 65
    17 66
    53 66
    55 63
    67 67
    75 67
    556 67

    I currently have all the requirements from sheet1!B2:B329 in a list on Col A on sheet 2!ColA
    "=IF(ROW() > COUNT(Sheet1!$B$2:$FL$329)," ", (IF(SMALL(Sheet1!$B$2:$FL$329,ROW())= " ","X",SMALL(Sheet1!$B$2:$FL$329,ROW()))))"

    Question: how to grab the page number from sheet1!Col A and place those in correct places on Sheet2!ColB (only column B)
    (NOTE: Some requirements are on multiple pages - notice requirement 9 (NOTE in NOTE: some are on as many as 8 pages))

    My choices are to create the sheet2! list (listed above) or use another column for removing duplicates in sheet2!ColA and them use multiple columns for the pages on sheet2

    I am open to ideas...I have about 5000 requirements to map to the document pages - need back and forth traceability for the customer.
    Last edited by excel-card-pulled; 04-07-2017 at 09:04 AM. Reason: solution was identified

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,640

    Re: Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "p

    It would help if you attached a sample Excel workbook.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

    Please note that the Paperclip icon does not work on this site.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    04-05-2017
    Location
    Floida, USA
    MS-Off Ver
    2013
    Posts
    4

    Re: Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "p

    Pete,

    Thank you for the note on how to upload the file...
    I have attached a large portion of the original document.
    Sheet 1 is the data extracted from another sheet - the only mod is I sorted on ColA.
    Sheet 2 is the test sheet, eventual results sheet.

    On sheet two, I have ColA filled in from sheet 1
    Then you will notice the small test sets I use for trial and error.

    I appreciate any assistance/advice you could provide.
    Attached Files Attached Files

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,640

    Re: Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "p

    You can get the values for column A in Sheet2 by using this formula:

    =SMALL(Sheet1!$B$1:$FL$328,ROWS($1:1))

    Copy down until you start to get blanks (or zeros).

    I'm not sure how you can then get the matching Page data, as you have duplicate values to lookup.

    Hope this helps.

    Pete

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,597

    Re: Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "p

    Hi excel-card-pulled. Welcome to the forum.

    I can do this with array formula ... for a small data set.

    This one requires too many array formulas. I've tried every helper column trick I know to avoid those. I've had no success.

    I tested some arrays anyway. Everything I come up with slows the workbook down ridiculously.

    Of note: Sheet1 B230 has this item among the numbers ... "1656 through 1800". I can't do anything with that even if I could come up with a sane solution.

    Maybe someone will be inspired.
    Dave

  6. #6
    Registered User
    Join Date
    04-05-2017
    Location
    Floida, USA
    MS-Off Ver
    2013
    Posts
    4

    Re: Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "p

    yes, I should have deleted that. I was reducing the data and sometimes put in reminders.

    I do this once in a while - not a daily report - we create the x-ref so verification can happen. slow is not a problem. Once the data is correct in the list I will perform the verification and send the list to the customer so they can verify it as well.

    I have been avoiding it, but I think I may need to write a macro -

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,597

    Re: Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "p

    So you would be copying and pasting values?

    Would you like to see one of the array files? They take about 25-30 seconds to calculate. Every non related edit you make after that is slow.

  8. #8
    Forum Contributor
    Join Date
    03-22-2017
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    230

    Re: Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "p

    From what this i see from previous post,

    the duplicate value can use =table+row(table)/1000000 to become an unique number. the extra amount is not significant as well.

    and attached my try version. hope it help.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    04-05-2017
    Location
    Floida, USA
    MS-Off Ver
    2013
    Posts
    4

    Re: Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "p

    BoredWorker - What a unique and very smart approach.
    I wish I had thought of that for the dups.

    Looks like you have achieved what I was trying to accomplish.
    I really appreciate everyone's inputs/assistance.

    I am glad I reached out. I am doing a line-by-line verification this time to be certain and will use this in the future.

    Thank you again.

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,597

    Re: Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "p

    @BoredWorker
    Interesting approach. It never occurred to me to add the decimal like that. I kept using a COUNTIF for the k argument in SMALL. It's slow.

  11. #11
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,597

    Re: Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "p

    Got it and without array formulas. The calc times are down to @ 18 seconds for the final formula. It still lags and recalculates on every irrelevant edit though, and I can't figure out why.

    None of these need to be committed CSE ... all committed by enter.
    In column A
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In column B to distinguish the multiple occurrences of numbers
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The final formula in column C
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by FlameRetired; 04-07-2017 at 09:32 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 02-05-2019, 12:03 AM
  2. [QUESTION/HELP-VBA/MACROS]Auto Edit Link from "Sheet1" to "Sheet2"
    By iphone3g in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 08-24-2016, 02:49 AM
  3. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  4. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  5. Find "Apple" in ColB, then count ColC until the next "Total" is found in ColB
    By roothog in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-08-2013, 10:30 PM
  6. [SOLVED] If there is any text in column "A$" on "sheet1" then move cell to column "A$" on "sheet2"
    By ckgeary in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-27-2013, 08:28 PM
  7. [SOLVED] use variable in Workbooks("book1").Worksheets("sheet1").Range("a1"
    By Luc in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-28-2005, 04:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1