Need help with formatting this worksheet, might require VBA

[mfitz51]

Hi, I am trying to group individual samples into pools of samples. I need to be able to say how many samples will be going into the pool, and what the pool will be called. After the pools are assigned, I need them to move to my export format for downstream use. In the downstream, all the samples of a pool must be listed in one cell.

Below, the raw import file of all the sample data. Could be 90-2000+ samples

Library Pooling Template.xls
	A	B	C	D	E
1	Entity	Source	Input Sample	UDI lot	Prep date
2	GI762_Lib	DNA-020	GI762	UDI_1 002	3/19/25
3	GI763_Lib	DNA-021	GI763	UDI_2 002	3/19/25
4	GI764_Lib	DNA-022	GI764	UDI_3 002	3/19/25
5	GI765_Lib	DNA-023	GI765	UDI_P4 001	3/19/25
6	GI766_Lib	DNA-024	GI766	UDI_P5 001	3/19/25
Raw Import

Next, all the samples (Libraries) will be assigned to a pool.
Needs
1. User input of number of libraries (samples) per pool (B3)
2. The pool has an alphanumeric ID that will be assigned (B6-B15) and needs to populate in column D according to the number of samples per pool (B3)
3. Column F will populate & concatenate with =CONCAT('Raw Import'!A2,", ") It needs to have the comma at the end for the down stream application
4. Column G is directly from 'Raw Import'!D, doesn't need manipulation

Library Pooling Template.xls
	A	B	C	D	E	F	G
1	Information			Pool #	Sample #	Sample ID	UDI #
2	Total Samples:	94		1234	1	GI762_Lib,	UDI_1 002
3	# of Libraries per Pool:	10		1234	2	GI763_Lib,	UDI_2 002
4	# of Pools total	9.4		1234	3	GI764_Lib,	UDI_3 002
5				1234	4	GI765_Lib,	UDI_P4 001
6	Pool ID # to be used	1234		1234	5	GI766_Lib,	UDI_P5 001
7		ABCD		1234	6	GI767_Lib,	UDI_P6 001
8		5678		1234	7	GI768_Lib,	UDI_P7 001
9		EFGH		1234	8	GI769_Lib,	UDI_P8 001
10		91011		1234	9	GI770_Lib,	UDI_P9 001
11		IJKL		1234	10	GI771_Lib,	UDI_P10 001
12		1213		ABCD	1	GI772_Lib,	UDI_P11 001
13		MNOP		ABCD	2	GI773_Lib,	UDI_P12 001
14		1415		ABCD	3	GI774_Lib,	UDI_P13 001
15		QRST		ABCD	4	GI775_Lib,	UDI_P14 001
16				ABCD	5	GI776_Lib,	UDI_P15 001
17				ABCD	6	GI777_Lib,	UDI_P16 001
18				ABCD	7	GI778_Lib,	UDI_P17 001
19				ABCD	8	GI779_Lib,	UDI_P18 001
20				ABCD	9	GI780_Lib,	UDI_P19 001
21				ABCD	10	GI781_Lib,	UDI_P20 002
Pool Assignment

Cell Formulas
Range	Formula
B2	B2	=COUNTA('Raw Import'!A2:A2000)
F2:F21	F2	=CONCAT('Raw Import'!A2,", ")
G2:G21	G2	='Raw Import'!D2
B4	B4	=B2/B3
D2:D11	D2	=$B$6
D12:D21	D12	=B$7

Next, all the pools need to be converted to the export format for the next downstream step.
Needs
1. Pool alpha numeric ID needs to populate in column c
2. The libraries/samples must populate in ONE cell per pool ID in column D. This is critical for down stream usage
Columns A & B left empty intentionally

Library Pooling Template.xls
	A	B	C	D	E
1	Entity	Project Code	Pool ID	Libraries	Pooling Date
2			1234	GI762_Lib, GI763_Lib, GI764_Lib, GI765_Lib, GI766_Lib, GI767_Lib, GI768_Lib, GI769_Lib, GI770_Lib, GI771_Lib	3/21/25
3			ABCD	GI772_Lib GI773_Lib GI774_Lib GI775_Lib GI776_Lib GI777_Lib GI778_Lib GI779_Lib GI780_Lib GI781_Lib	3/21/25
4			5678	GI782_Lib GI783_Lib GI784_Lib GI785_Lib GI786_Lib GI787_Lib GI788_Lib GI789_Lib GI790_Lib GI791_Lib	3/21/25
Export Format

Cell Formulas
Range	Formula
C2	C2	='Pool Assignment '!D2
C3	C3	='Pool Assignment '!D12
C4	C4	='Pool Assignment '!D22
E2:E4	E2	=TODAY()

 

[Sam_D_Ben]

Please test whether this code is working correctly, or provide additional raw data for correction, Thank You

Sub x()
    Dim a As Worksheet, b As Worksheet, c As Worksheet
    Dim d As Long, e As Long, f As Long
    Dim g As Long, h As Long
    Dim i As Integer, j As Integer
    Dim k As String, l As String
    
    Set a = ThisWorkbook.Sheets("Raw Import")
    Set b = ThisWorkbook.Sheets("Pool Assignment")
    Set c = ThisWorkbook.Sheets("Export Format")
    
    d = a.Cells(a.Rows.Count, 1).End(xlUp).Row
    e = b.Cells(b.Rows.Count, 2).End(xlUp).Row
    j = b.Range("B3").Value
    
    c.Cells(1, 1).Value = "Entity"
    c.Cells(1, 2).Value = "Project Code"
    c.Cells(1, 3).Value = "Pool ID"
    c.Cells(1, 4).Value = "Libraries"
    c.Cells(1, 5).Value = "Pooling Date"
    
    g = 6
    h = 2
    Dim m As Long: m = 2
    Dim n As Long: n = 2
    
    Do While g <= e And h <= d
        k = b.Cells(g, 2).Value
        l = ""
        
        For i = 1 To j
            If h > d Then Exit For
            
            b.Cells(m, 4).Value = k
            b.Cells(m, 5).Value = i
            b.Cells(m, 6).Value = a.Cells(h, 1).Value
            b.Cells(m, 7).Value = a.Cells(h, 4).Value
            
            If l = "" Then
                l = a.Cells(h, 1).Value
            Else
                l = l & vbNewLine & a.Cells(h, 1).Value
            End If
            
            h = h + 1
            m = m + 1
        Next i
        
        c.Cells(n, 3).Value = k
        c.Cells(n, 4).Value = l
        c.Cells(n, 4).WrapText = True
        c.Cells(n, 5).Formula = "=TODAY()"
        
        g = g + 1
        n = n + 1
    Loop
    
    
End Sub