Forum Discussion
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 ...
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!
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!
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 SubDoes this code return a result similar to what you are looking for when you run the macro in the attached file?
- I have switched off my laptop. But I will check this I the morning. Thank you very much.