Combining Multiple Rows with same uniquie identifier

Good day to everyone.

I am very green at using excel. I find myself in a position where I have to modify, edit a csv file and quite honestly - after searching the web for many days I can't say I have found the right solution for my question.
I know merging cells, concatenating is fairly common - but I am not sure how common my request is.
I'll explain and post a snippet. I have a CSV file - 4 columns. There is a unique identifier available. I am trying to combine; organize; move all like rows - accordingly to the unique identifier onto one row. (I have to import this into a tool and the way this csv file is, each row will overwrite the previous row. There isn't any appending options here) NOTE: Each unique identifier could be on 6 rows upto perhaps 30 rows. It's basically structured for Additional Features and a Description.
(product features: some products have six features, some products have 30 features.) Attached here is what I currently have. The second attachment is what I am after. My apologies but the actual attachment paperclip option is not working for me. The column names, I do not think have any relevancy - I used feature and desc as an example. But I have to add, there isn't any consistency with the number of rows. I have products that use upt 30 rows. Any advice, assistance would greatly be appreciated by this noob.

prod_details.PNG

How I want Prod Desc CSV.PNG

I need to mention that COLUMN C looks to be consistent with its naming convention - it is not. The SEQUENCE column (I have yet to find the commonality because I have found SEQ and it's value associated with different.
There are many products that have a Name OTHER FEATURES in Column C. Unfortunately those instances could have numerous rows. Example attached.

Other Features Example.PNG

Welcome to the forum

Look below and it tells you another way to post a file. We need 2 sheets
- the before sheet
- the "What I want to see" sheet

As I understand it you want
- everything for the same item no on the same row

Q1 Does that mean ItemNO /SeqNo / Feature / descr /SeqNo / Feature / descr /SeqNo / Feature / descr / ....?
and (perhaps more importantly)...
Q2 Do you want ALL Seq 10 to be in one column, ALL Seq 11 to be in column etc?

Hi Kev and thank you for the welcome.

1st: Toss out the SEQ - there isn't any relevancy that I can find for its use.
So Itemno / Feature / Descr / Feature 2 / Descr 2 / Feature 3 / Descr 3 and so on depending of course on the number of rows per Unique Identifier.

As mentioned some ITEMNO contain six rows and upto perhaps 30 rows.

Thanks
Terry

PS: if you look on the 3rd attachment: 00037 OTHER FEATURES You'll notice several descriptions listed but missing the OTHER FEATURES text beside it. I'd be happy just to merge that specific group of cells into one if that makes any sense. I'm assuming it's going to require some VBA, way out of my league.

VBA is the way I would go

You did not answer Q2
Do you want ALL Seq 10 to be in THEIR OWN column, ALL Seq 11 to be in THEIR OWN column etc?

Please attach a workbook - saves me having to recreate..
thanks

to add attachment - see bottom of my first post - ignore the paperclip

thanks.
got it and will look at it in a couple of hours.

See if this does what you want.
- Try it first with the cut-down database - Sheet "Additional Details" now only contains 220 lines.
- Run it in the attached workbook with {CTRL} + t
- It runs almost instantly.

Then try it with the complete database by copying the vba below into your original file.
I fear that it will take a long time to run with so many rows. Will look to make it faster if very slow.

I'm signing off for the night. I look forward to your feedback


Put in a general module:

Please Login or Register  to view this content.

Apologies - when I read back what I wrote I can see why you misunderstood
The result is in Sheet "Additional Details" - is that where you are looking? It may be messed up if you have run the macro several times - it will look rather different to the image below
It may be easiest to delete the file and download it and try again
- open it and enable edit and macros etc
- leave it open on Sheet "Additional Details"
and run the macro with {CTRL} t (hit and hold control key when you hit the t)

How the Sheet "Additional Details" should look
OneRow.jpg

I hope your wife is feeling much better.
Next try to run the VBA with the complete data set - I remember being concerned that it would take quite a long time to run.
So to test, either:
A) paste all the data into sheet Additional Details in the workbook I posted
OR:
B) insert the VBA in a general module in your own workbook which contains all the data in Sheet Additional Details
And see how long it takes. If it takes too long I'll have a re-think
Kev

Close all your other applications and files - running this macro will need all the PC's resources
Choose a time when you do not need to use your PC for a while.

Open up the csv file in Excel
- Immediately re-save it as a "macro-enabled workbook"
- Open VBA window with {Alt} {F11}
- Insert a module
- Paste in the VBA
- Close VBA window ( X in top right corner)
- Save the file (do this before running the macro)
- Run the macro
- How long does it take to run?
(30 minutes?)

SaveAs.jpg

VBA window.jpg

VBA window2.jpg

RunMacro.jpg

You are welcome.
Would appreciate it if you mark the thread as solved after you have verified all is ok.
thanks
Kev

Always happy to entertain questions - may not be able to answer it though!
Perhaps best to start a new thread.

Heh. Well I'm impressed as heck anyways. The VBA script worked like a champ. The only thing that is hampering me is, when the data is moved to its appropriate line in the new fields - is there anyway to keep the columns/fields consistent?
IE: Lets use BULLET TYPE for example. It shows up in several columns. BULLET WEIGHT as another example might show up in several columns.
So how that hampers me is I have to import this CSV file into a web application. Drag and drop. Header / description.
If I was able to align all equal/same headers in the same column(s) it would make my importing into another app easier. I can predict the order and choose what I need.
The way it is now, I dont know which column the BULLET TYPE heading might be in.

Does that make sense?

The attachment shows a FIND I did. In the CELL you will see J, H, L N and so forth. I'd love to have all my "criteria" in

Yes VBA can give you what you need. But we need to take step back to go forward. As I am away from my PC, I am unable to examine your original data file, but from memory
- seq 0 to 79 have unique "features"
- seq 80 and above do not

So let's use those "features" as our column headers up to seq 79, and from 80 onwards we use "Feature 80", "Feature 81" etc. A separate column is required for each seq - this ensures that everything is collated in the way you want.
To achieve this, VBA will
1. add "Feature 80", "Feature 81" etc where column C in your original data file contains blanks
2. sort the data by item number then by seq - this should make the next step quicker
3. Use whichever method is quickest to match the item's seq to the correct column (I need to test on your data)
4. Place the feature "description" in the correct colum for each item

I will update the thread with the code when I get back to my PC

Thanks - it's time for a quick backpedal then:
- column headers will be either the text in column C or (where column C is blank) "Feature 80", "Feature 81" etc.

Can you run a few queries - seq 80+ versus the description in column D to see if you can discern any pattern/logic?
- can we use seq 80, 81 etc to group common features
- or were seq nos allocated at random when the items were set up?

It would be good if we could make the columns collect the same type of descriptions together if there is some discernable logic. But perhaps this will need to be done separately for seq 80+ (perhaps using a helper column etc)

Have a go with this code - this may be pretty close to what you are looking for.
See attached workbook (which contains the code)
- sheet "Additional Details" - contains a few of your original records
- sheet "Results" - show what those few items look like after being processed
- header text repeated in cells (I am not sure if you want it there or not)
- sheet "Errors" - blank in attached file but when you run it against complete data set expect a handful of items to be listed (VBA steps over the errors - they are due to invalid data resulting from the import)

This code runs faster than the previous code and I have added a timer.
Let me know what you think.


What you need to do:
Replace all the previous code with this and give it a go against your data
(VBA assumes that the values are held in sheet named "Additional Details")

Please Login or Register  to view this content.