Forum Discussion

crankcaller's avatar
crankcaller
Copper Contributor
Aug 16, 2023

Duplicate Row if any data in particular cell

 

Hello,

I have a spreadsheet of books. Roughly 3000 rows.
Columns A to E are ISBN, Author etc.
Columns F to N are Barcodes. Not all cells have barcodes.

 

If the title has one copy then there is only a barcode in F
If it has 3 copies there are barcodes in F, G, H etc.

 

All the barcodes begin with R.

 

I wish to duplicate the Row if there is a barcode in G to N so I can then have 3 rows for a title if it has three copies. 6 rows for a title if it has 6 rows etc.

 

I don't know any VBA or anything.

I filtered column G to show rows with data and there were 373 - so manually would take forever.

Thanks for reading this.

 

I have Office 365 if that makes a difference.

 

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor

    crankcaller

     

    If you're looking for a formula (or a custom function), this will do it:

     

    'ReShape Lambda
    =LAMBDA(range,LET(
        header, HSTACK(TAKE(range, 1, 5), "Barcode"),
        data, DROP(range, 1),
        record, TAKE(data, , 5),
        barcodes, TAKE(data, , -9),
        bc_vector, TOCOL(barcodes, 1),
        StackBooks, LAMBDA(a, v,
            LET(
                c, SUM(MMULT((barcodes = v) * 1, SEQUENCE(9))),
                title, XLOOKUP(v, CHOOSECOLS(barcodes, c), record, ""),
                VSTACK(a, HSTACK(title, v))
            )
        ),
        REDUCE(header, bc_vector, StackBooks)
    ))

     

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    crankcaller Based on the example data in OliverScheurich 's file I created a simple Power Query solution. It produces a table where the first five columns repeat for every barcode entered in the following columns.

    Since this will be a one-time procedure (as I understand) I didn't make anything fancy. But it shouldn't be much of a problem to run this on a table with 3000 rows. Are you familiar with PQ?

     

  • crankcaller's avatar
    crankcaller
    Copper Contributor
    Ok, using filter & copy and paste I now have sheets for each column G to N.
    Would it be easier to merge those sheets into the first one?
    Using say the ISBN to copy the row from each sheet and insert it under the original?



    • mathetes's avatar
      mathetes
      Silver Contributor

      crankcaller 

      I wish to duplicate the Row if there is a barcode in G to N so I can then have 3 rows for a title if it has three copies. 6 rows for a title if it has 6 rows [barcodes?] etc.

      ...

      Ok, using filter & copy and paste I now have sheets for each column G to N.
      Would it be easier to merge those sheets into the first one?
      Using say the ISBN to copy the row from each sheet and insert it under the original?

       

      Before you take any further action,  actions that may or may not actually be necessary, why don't you. back up and tell us what your purpose or goal is. You're proposing (or taking) actions manually to break things apart (for what reason?) when Excel can actually do a LOT of that kind of heavy lifting itself from a well designed data table.

       

      It sounds as if you're starting point--that 3,000 rows of book data-- could serve just fine as a source of data for various kinds of summary counts, etc. But you need to describe what your goal or purpose is; then let the Excel folks help with how Excel can do the work.

       

      • crankcaller's avatar
        crankcaller
        Copper Contributor

        mathetes 

        You are quite correct in what you say! I know there will be a way to do it more elegantly than what I am able to do with my very basic Excel knowledge.

        I wish to compare a list of books from a library system with a list of books (the spreadsheet)

        The spreadsheet has one ISBN per row and is an export from an old library system.

        Each row has multiple  barcodes in columns F to N, but only one copy of the Bibliographic info - ISBN, Author, Title etc. Columns  A to E.

         

        I wanted to duplicate columns A to E if there was info in rows G to N.

         

        IsbnFirst AuthorTitleClassificationSectionBarcode Barcode 2Barcode 3Barcode 4Barcode 5Barcode 6Barcode 7Barcode 8
        9780140621167Poe, Edgar AllanSelected TalesE POEExploreR07862K0554R07861J0554R07860A0554R07859W0554R07858F0554R05666K0554R05667L0554R05668M0554

         

         So in that example above it would duplicate that row seven times.

        That's part 1!

         

        Part 2 would be replace the barcode Field in the second row from barcode 2, replace the barcode field  in row 3 from Barcode 3 etc etc.

         

         

         

Resources