Duplicate Row if any data in particular cell

[crankcaller]

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.

[#2]

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?

[#3]

@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.

[#4]

@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.

Isbn

First Author

Title

Classification

Section

Barcode

Barcode 2

Barcode 3

Barcode 4

Barcode 5

Barcode 6

Barcode 7

Barcode 8

9780140621167

Poe, Edgar Allan

Selected Tales

E POE

Explore

R07862K0554

R07861J0554

R07860A0554

R07859W0554

R07858F0554

R05666K0554

R05667L0554

R05668M0554

 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.

[#5]

@crankcaller 

You're giving us what I'd call a "procedural" or "mechanical" answer to my question of "What are you trying to accomplish?" 

I know you want to compare two lists, a list of books from a library system with a list of books (the spreadsheet), but I'm trying to find out what it is that this multiplication of rows is going to do that can't be done with the data as it exists. 

Said another way--or perhaps it's a different question--what kind of comparison are you trying to do that causes you to think you need multiple rows per book.

Said another way, what might the final output look like?

Try to answer those last two questions in plain ol' English, i.e., without reference to rows and columns in Excel. Don't try to do the Excelling for us; let us do that.

(For the record, these are the kinds of questions I'd be asking if we were sitting down face-to-face.)

[#6]

The person who compiled the spreadsheet has possibly messed up somewhere. Not me i'm glad to say - but i'm having to fix it!

The items were imported into a new system from the spreadsheet and some titles have the wrong barcode entirely.

We know this as when you search for some items with the book in front of you the barcode doesn't match, they are for a different item entirely. I checked the titles in question on the original spreadsheet and they are wrong there as well - so presumably it wasn't an error when the spreadsheet was mapped to the database.

I wish to scan the ISBN and barcode from each physical item into a file. This will give me the correct info.

I then wish to compare that to the system and original spreadsheet.

Because of multiple copies of items with the same ISBN I wanted one per row - I assumed it would be easier to compare the multiple copies by sorting by ISBN.

Sorry if this is too much info - I've been at this a while today and my head is fried!

[#7]

@crankcaller 

Sub barcodes()

Dim i, j, k As Long

j = Range("A" & Rows.Count).End(xlUp).Row

For i = j To 2 Step -1

For k = 13 To 6 Step -1

If Cells(i, k).Value <> "" Then

Cells(i + 1, 1).EntireRow.Insert

Range(Cells(i + 1, 6), Cells(i + 1, 13)).Value = Range(Cells(i, 6), Cells(i, 13)).Value
Range(Cells(i + 1, 1), Cells(i + 1, 5)).Value = Range(Cells(i, 1), Cells(i, 5)).Value
Cells(i + 1, k).Clear

Else

End If

Next k

Next i

End Sub

Does this code return a result similar to what you are looking for when you run the macro in the attached file?

[#8]

@crankcaller 

Sorry if this is too much info - I've been at this a while today and my head is fried!

I understand. Reading what you've written, my head is spinning. But I have a question, based on the following two statements:

I wish to scan the ISBN and barcode from each physical item into a file. This will give me the correct info.
I then wish to compare that to the system and original spreadsheet.

Once you have the correct info, after scanning each physical item, what's the purpose of comparing with the system and the original spreadsheet? You have the correct info; why not stop there? What additional value is achieved by comparing the correct info with info you know not to be fully reliable?

[#9]

I have switched off my laptop. But I will check this I the morning. Thank you very much.

[#10]

As I typed my previous response I realised that myself. I would like to know if they are all wrong - or what percentage. If they are all wrong then a new load will be required which will cost money. If it's say 25% of items then staff can manually alter the records.

[#11]

So you seem to be suggesting that the records--official library records?--won't automatically get corrected by the scanning process; is that right? There still seem to be a lot of moving parts here, and it's difficult to get the full picture "remotely."

[#12]

@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?

[#13]

@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)
))