Complicated (?) Excel question

[extraT]

I have a sheet with data in columns A-L. This data can be up to 500 rows deep.
Coloumn I has a code. Codes may appear multiple times (in a random order, I can sort it ascending / decending so all rows with the same code and therefore the same info in all other rows are "grouped" together
.
The codes may change on a monthly basis, but coloumn I is always the codes.

What I would need is is macro / VBA to read the codes and take all information in that row and copy -paste it into a new sheet.
Keeping in mind the codes could change on a monthly base, is there anyway to do this?

example:

Coloumn I
1
4
4
5
1
1

The macros would read the codes copy 3 x 1 along with all info in a new sheet. the same thing with 2 x 4 and 1 x 5 (if that makes sense).

Or do I have to do this manually?

Thanks in advance!

ET.

[Doofus]

You want to sort into code order?

[57Ford]

Or do you want a count of all identical lines so it appears on the new sheet as 4x 500g boxes of cornflakes with the code of CF500G and 2x 100g packets of porridge with a code of PR100G…?

[Harpoon]

Would using Unique and CountIf functions work?

You'd have to copy manually to another sheet but avoids needing a macro

https://www.statology.org/excel-count-occurrences/

[extraT]

I knew this would be complicated

The complicated part is the products (and therefore the codes) can change per month - but the codes are ALWAYS in coloumn I.
I suppose what i'm asking is if its possible Excel to recognise duplicates / multiples and copy all info into a new sheet based on the codes.

I can sort the codes ascending / decending via filter if that helps.

[extraT]

Harpoon said:

Would using Unique and CountIf functions work?

You'd have to copy manually to another sheet but avoids needing a macro

https://www.statology.org/excel-count-occurrences/

Copying manually is the bit I want to avoid

If I copied manually, I could just set a filter to sort decending and do things the good ol' fashioned way! Thank you anyway, I appreciate it!

[Doofus]

extraT said:

I can sort the codes ascending / decending via filter if that helps.

So can you just sort and then subtotal using the data function?

[simon_harris]

You probably want to use the power query functionality, it is part of the package now but not always switched on. It can do what you ask and very much more.

[Pistom]

This is what ChatGPT says;


"Yes, you can automate this task with a macro/VBA script. Here's a simple example of how to do this:

Open the Excel sheet and press Alt + F11 to open the VBA editor.
In the editor, insert a new module and paste the following code:

Sub CopyRowsWithSameCode()

Dim lastRow As Long
Dim currentCode As String
Dim newSheet As Worksheet

lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "I").End(xlUp).Row

Set newSheet = ThisWorkbook.Sheets.Add
newSheet.Name = "Copied Rows"

For i = 1 To lastRow
If i = 1 Then
currentCode = ActiveSheet.Cells(i, 9).Value
ActiveSheet.Rows(i).Copy Destination:=newSheet.Range("A" & newSheet.Rows.Count).End(xlUp).Offset(1)
Else
If ActiveSheet.Cells(i, 9).Value = currentCode Then
ActiveSheet.Rows(i).Copy Destination:=newSheet.Range("A" & newSheet.Rows.Count).End(xlUp).Offset(1)
Else
currentCode = ActiveSheet.Cells(i, 9).Value
ActiveSheet.Rows(i).Copy Destination:=newSheet.Range("A" & newSheet.Rows.Count).End(xlUp).Offset(1)
End If
End If
Next i

Save the macro and close the editor.

To run the macro, press Alt + F8 and select the CopyRowsWithSameCode macro.

This code will copy all the rows with the same code in column I to a new sheet named "Copied Rows"."

Edited by Pistom on Wednesday 8th February 09:13

[Anubis]

You don’t need to over complicate it.

1. Highlight all your data (columns and rows) and create a pivot table

2. Put column I (codes) as pivot table rows. Also put column I (codes) in the pivot table summary and change it to count the rows. You will now get something like:

A 1
B 4
C 2
…

(1 A code, 4 B codes and so on)

3. If you want to see the raw data for each of these double click on the summary (the number) in the pivot table. It will automatically drill down and open a new sheet for you showing you the rows you need. For example, double clicking on the 4 next to B will show you the four B (code) rows.

This may sound more complex than it really is so check out this link:

https://www.contextures.com/excel-pivot-table-dril...

Since your data is now in a pivot table you just right click and refresh it whenever your codes change. The pivot table will update for you with new counts for each column I codes.

Edited by Anubis on Wednesday 8th February 22:19

[paulrockliffe]

I tried to say that yesterday, but I couldn't post to the thread, just got a 403 error all day.

[bigpriest]

If you have a dynamic list of all the unique product codes, you could use that on a separate sheet and just use COUNTIF to reference column I on the data sheet.

[paulrockliffe]

You could, but what the OP has is a mini database, his solution should really aim to move away from formulas entirely, they're not necessary for structured data and haven't been for a decade now.

[OutInTheShed]

extraT said:

I have a sheet with data in columns A-L. This data can be up to 500 rows deep.
Coloumn I has a code. Codes may appear multiple times (in a random order, I can sort it ascending / decending so all rows with the same code and therefore the same info in all other rows are "grouped" together
.
The codes may change on a monthly basis, but coloumn I is always the codes.

What I would need is is macro / VBA to read the codes and take all information in that row and copy -paste it into a new sheet.
Keeping in mind the codes could change on a monthly base, is there anyway to do this?

example:

Coloumn I
1
4
4
5
1
1

The macros would read the codes copy 3 x 1 along with all info in a new sheet. the same thing with 2 x 4 and 1 x 5 (if that makes sense).

Or do I have to do this manually?

Thanks in advance!

ET.

Crude method:
Copy The whole thing into 5 new pages.
On page one, delete all the data that's not a 'code 1'
On page two, " " 2
etc.

[RizzoTheRat]

paulrockliffe said:

You could, but what the OP has is a mini database, his solution should really aim to move away from formulas entirely, they're not necessary for structured data and haven't been for a decade now.

Agree with this. Power query and power pivot are really easy to use, I'd be inclined to covert the existing data in to a table, import it in to power query. Once you have it as a query generating a a table of the unique codes is trivial, and then you can generate all the output tables you want.
Loads of videos on youtube on use Power Query, it really doesn't take long the learn the basics.

[FlyingPanda]

I’d be fascinated to know if the ChatGPT answer posted earlier actually works.

This could become the modern version of “LetMeGoogleThatForYou” (if it hasn’t already).

[paulrockliffe]

RizzoTheRat said:

paulrockliffe said:

You could, but what the OP has is a mini database, his solution should really aim to move away from formulas entirely, they're not necessary for structured data and haven't been for a decade now.

Agree with this. Power query and power pivot are really easy to use, I'd be inclined to covert the existing data in to a table, import it in to power query. Once you have it as a query generating a a table of the unique codes is trivial, and then you can generate all the output tables you want.
Loads of videos on youtube on use Power Query, it really doesn't take long the learn the basics.

They're only really easy to use if you know though and if you know, you wouldn't be asking the OPs question. I think it's probably over-kill for what the OP asked, compared with just formatting as a table and creating a Pivot Table from that, but at the same time it's probably unhelpful to suggest VBA or Formula-based solutions, even though they do work.

You want every spreadsheet to be part of a journey that ends with you understanding data properly, then if you still want to use Excel you'll know that it IS a database. Formulas and VBA just muddy that journey horrifically.

But yeah, definitely learn Power Query if you want to do this properly.

[extraT]

Hi All,

Firstly, thank you for your all your answers, they are appreciated and apologies for not updating earlier.

As it turned out, after some internal discussions, we didnt need to do anything overly complicated and a simple pivot table was enough.

Thank you again for your potential solultions, I appreciate it!

ET.