Array index problems, need assistance or any advice to help to create formula to enable to operate worksheet.

[teresafinance]

Hi All, I am trying to figure out a formula that find the data of families with one, two, three, four, or five children in our database. In the first worksheet is where the data is compiled. Column 1 - Family ID, Colum 2 - No of children in Family, and column 3 Student IDs relevant to each family. In worksheet 2 I have the same data that is extracted from our system on a ms query that has the Student ID listed.

I have attached an example of what I am working with. What formula would I need to put into cell c3:C1500 to obtain the information needed? I know very little about Index and arrays and what I have tried thus far is not working and returns a #N/A result.

 

[Peter_SSs]

Welcome to the MrExcel board!

Given that you have Excel 2016 and it seems you want to list all the family IDs in a single cell, you would not be able to use standard worksheet formulas. Would a vba solution be acceptable?

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with. Also a good idea to include the expected results, entered manually, for your sample data so we know exactly what you are aiming for.

 

[Dossfm0q]

MRExcel test.xlsx
	A	B	C
1	Information in Spreadsheet 1
2	Family Id	No Of Ch	Student ID
3	2022	2	1000
4	2023	2	1001
5	2023	2	1002
6	2841	2	1003
7	2841	2	1004
8	2405	3	1005
9	2026	2	1006
10	2378	3	1007
11	2377	1	1008
12	2378	3	1009
13	2028	3	1010
14	2691	2	1011
15	2331	1	1012
Sheet1

Cell Formulas
Range	Formula
C3:C15	C3	=1000+(COUNTA($A$3:A3)-1)

 

[teresafinance]

Welcome to the MrExcel board!

Given that you have Excel 2016 and it seems you want to list all the family IDs in a single cell, you would not be able to use standard worksheet formulas. Would a vba solution be acceptable?

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with. Also a good idea to include the expected results, entered manually, for your sample data so we know exactly what you are aiming for.

Im sorry I dont know what an XL2BB is. I can upload an excel file if required, but otherwise, I can't help on that one. And on a work computer, so I cannot download plug ins.

 

[Peter_SSs]

I can upload an excel file

You can't upload files to the forum, but you could upload elsewhere and provide a shared link here (though also due to workplace restrictions some potential helpers will be prevented from accessing the file). Another option is to include the expected results manually for the sample data and just copy/paste here. Example:

6​	1​	6​
7​	9​	1​
8​	7​	5​
1​	7​	9​

 

[teresafinance]

You can't upload files to the forum, but you could upload elsewhere and provide a shared link here (though also due to workplace restrictions some potential helpers will be prevented from accessing the file). Another option is to include the expected results manually for the sample data and just copy/paste here. Example:

6​	1​	6​
7​	9​	1​
8​	7​	5​
1​	7​	9​

Here is the link, thanks Book1.xlsx

 

[Dossfm0q]

Public Sub Test()
Dim Rng As Range
Dim LRw As Long, N As Long
LRw = Cells(Rows.Count, 1).End(xlUp).Row
N = 0
    For Each Rng In Range("A3").Resize(LRw, 1)
        If Rng <> "" Then
            N = N + 1
            Rng.Offset(0, 2) = 1000 + N - 1
        End If
    Next
    
End Sub

 

[teresafinance]

Public Sub Test()
Dim Rng As Range
Dim LRw As Long, N As Long
LRw = Cells(Rows.Count, 1).End(xlUp).Row
N = 0
    For Each Rng In Range("A3").Resize(LRw, 1)
        If Rng <> "" Then
            N = N + 1
            Rng.Offset(0, 2) = 1000 + N - 1
        End If
    Next
   
End Sub

Ok, thank you. I don't have any knowledge of VBA, I just copied and pasted and it has not compiled anything. I assume this page assumes excel users are advanced, and I only have somewhat scattered knowledge.

 

[Peter_SSs]

Here is the link, thanks

I don't see how that 'AFTER' sheet was arrived at.
In the image below showing all 3 sheets I can see corresponding Family IDs linked by the red lines but I don't know what is going on with the 2 yellow values?
Also, how does ID 1001 get linked to Family ID 2022 in the AFTER sheet when ID 1001 is linked to Family ID 2023 in Sheet2?

 

[teresafinance]

I'm sorry my mind has been haywire! I think I have corrected the shared document, hopefully, it has all the correct data there now. This is so overwhelming, and alot going on at work.. Hopefully it is easier to understand now.. Book1.xlsx