Create term base from large data set

[chillzen]

Hello,

I am stuck trying to solve what may be a very simple problem. I understand logically what needs to be done I just don't have the knowledge for the best way to handle this much data and not make excel crash. I am currently limited with no access to utilize Pandas to solve this even though that seems to be the best option. If this can be done in Excel possibly using VBA that would be great. I am creating a 2-column term base for two languages (A and B).

Notes:
Columns A:N = Lang A
Columns O:AP = Lang B
Each row is a set of a word in LangA and its synonyms with LangB and its grammatical case variations
~26K rows total
Total of 67,431 LangA terms | 285,974 Lang B
Not all Language A terms have Language B equivalents and vise versa but still need to be returned (to fill in later with the translations)
Average of 2.5 LangA terms in each row and avg 10.5 LangB
Output needs to be 2 columns (may be split into multiple sheets/files if needed since it will obviously be an extremely large number of rows generated)

Below is the best sample I can provide that should help work towards a solution:

Raw Data

A Terms	A Terms	B Terms	B Terms
A1	A2	B1	B2
A3	A4	B3
A5		B4	B5
A6		B6
A7
		B7	B8
		B9

Desired Output

Term A	Term B
A1	B1
A1	B2
A2	B1
A2	B2
A3	B3
A4	B3
A5	B4
A5	B5
A6	B6
A7
	B7
	B8
	B9

 

[Eric W]

Welcome to the MrExcel forum!

Give this macro a try:

Sub Flatten()
Dim SrcSheet As Worksheet, TrgSheet As Worksheet, OutData() As String, MaxRows As Long
Dim MyData As Variant, OutCol As Long, ctr As Long, r As Long, c1 As Long, c2 As Long

    Set SrcSheet = Sheets("Sheet16")
    Set TrgSheet = Sheets("Sheet17")
        
    MaxRows = Rows.Count
    c1 = SrcSheet.Cells(Rows.Count, "A").End(xlUp).Row
    c2 = SrcSheet.Cells(Rows.Count, "O").End(xlUp).Row
    If c1 < c2 Then c1 = c2
    MyData = SrcSheet.Range("A1:AP" & c1).Value
    
    OutCol = 1
    ReDim OutData(1 To MaxRows, 1 To 2)
    OutData(1, 1) = "Term A"
    OutData(1, 2) = "Term B"
    ctr = 2
    
    For r = 2 To UBound(MyData)
    
        c1 = 1
        Do
            c2 = 15
            Do
                OutData(ctr, 1) = MyData(r, c1)
                OutData(ctr, 2) = MyData(r, c2)
                ctr = ctr + 1
                If ctr = MaxRows + 1 Then
                    TrgSheet.Cells(1, OutCol).Resize(MaxRows, 2).Value = OutData
                    OutCol = OutCol + 3
                    ReDim OutData(1 To MaxRows, 1 To 2)
                    OutData(1, 1) = "Term A"
                    OutData(1, 2) = "Term B"
                    ctr = 2
                End If
                c2 = c2 + 1
            Loop Until MyData(r, c2) = ""
            c1 = c1 + 1
        Loop Until MyData(r, c1) = ""
        
    Next r
    If ctr > 2 Then TrgSheet.Cells(1, OutCol).Resize(ctr - 1, 2).Value = OutData
    
End Sub

Put the name of the input sheet and the output sheet into the first two lines.

 

[chillzen]

Welcome to the MrExcel forum!

Give this macro a try:

Sub Flatten()
Dim SrcSheet As Worksheet, TrgSheet As Worksheet, OutData() As String, MaxRows As Long
Dim MyData As Variant, OutCol As Long, ctr As Long, r As Long, c1 As Long, c2 As Long

    Set SrcSheet = Sheets("Sheet16")
    Set TrgSheet = Sheets("Sheet17")
       
    MaxRows = Rows.Count
    c1 = SrcSheet.Cells(Rows.Count, "A").End(xlUp).Row
    c2 = SrcSheet.Cells(Rows.Count, "O").End(xlUp).Row
    If c1 < c2 Then c1 = c2
    MyData = SrcSheet.Range("A1:AP" & c1).Value
   
    OutCol = 1
    ReDim OutData(1 To MaxRows, 1 To 2)
    OutData(1, 1) = "Term A"
    OutData(1, 2) = "Term B"
    ctr = 2
   
    For r = 2 To UBound(MyData)
   
        c1 = 1
        Do
            c2 = 15
            Do
                OutData(ctr, 1) = MyData(r, c1)
                OutData(ctr, 2) = MyData(r, c2)
                ctr = ctr + 1
                If ctr = MaxRows + 1 Then
                    TrgSheet.Cells(1, OutCol).Resize(MaxRows, 2).Value = OutData
                    OutCol = OutCol + 3
                    ReDim OutData(1 To MaxRows, 1 To 2)
                    OutData(1, 1) = "Term A"
                    OutData(1, 2) = "Term B"
                    ctr = 2
                End If
                c2 = c2 + 1
            Loop Until MyData(r, c2) = ""
            c1 = c1 + 1
        Loop Until MyData(r, c1) = ""
       
    Next r
    If ctr > 2 Then TrgSheet.Cells(1, OutCol).Resize(ctr - 1, 2).Value = OutData
   
End Sub

Put the name of the input sheet and the output sheet into the first two lines.

Eric,

This seems to be on the right track! It managed to give me all of the pairs for the LangA (A:O) with the LangB pair in column P, but it is not returning the pairs for A:O to Q:AP.

 

[Eric W]

This is what I started with:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	A Terms	A Terms													B Terms	B Terms
2	A1	A2													B1	B2
3	A3	A4													B3
4	A5														B4	B5
5	A6														B6
6	A7
7															B7	B8
8															B9
Sheet16

And what I ended up with:

Book1 (version 1).xlsb
	A	B
1	Term A	Term B
2	A1	B1
3	A1	B2
4	A2	B1
5	A2	B2
6	A3	B3
7	A4	B3
8	A5	B4
9	A5	B5
10	A6	B6
11	A7
12		B7
13		B8
14		B9
Sheet17

Are these the proper columns? Do you have any gaps between words on the same row? If this looks ok, can you show me a few lines that do not work right? (The XL2BB tool is most helpful in showing the exact rows and columns of your data. Check out the button in the reply box. It's easy to download, install, and use.)

 

[chillzen]

This is what I started with:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	A Terms	A Terms													B Terms	B Terms
2	A1	A2													B1	B2
3	A3	A4													B3
4	A5														B4	B5
5	A6														B6
6	A7
7															B7	B8
8															B9
Sheet16

And what I ended up with:

Book1 (version 1).xlsb
	A	B
1	Term A	Term B
2	A1	B1
3	A1	B2
4	A2	B1
5	A2	B2
6	A3	B3
7	A4	B3
8	A5	B4
9	A5	B5
10	A6	B6
11	A7
12		B7
13		B8
14		B9
Sheet17

Are these the proper columns? Do you have any gaps between words on the same row? If this looks ok, can you show me a few lines that do not work right? (The XL2BB tool is most helpful in showing the exact rows and columns of your data. Check out the button in the reply box. It's easy to download, install, and use.)

Maybe I could have been more clear in my example, apologies! Here is a screenshot of what the top of the file looks like. (I hid E:N because they are much like A:B) For A:O there should be no gaps between cells in the same row, and between P:AP no gaps either. I can try the XL2BB tool if the screenshot isn't sufficient.

 

[Eric W]

I don't understand. Column P looks entirely empty, as does T and V. Column R has a smattering of words. That's what I meant by "gaps". On row 2, there is a gap in columns P, R, T, and V. That's how my macro knows how to quit looking at a row, when it encounters an empty cell. Do you want me to look through every column in A:N, and every column in O:AP to see if there's something in it?

 

[chillzen]

I don't understand. Column P looks entirely empty, as does T and V. Column R has a smattering of words. That's what I meant by "gaps". On row 2, there is a gap in columns P, R, T, and V. That's how my macro knows how to quit looking at a row, when it encounters an empty cell. Do you want me to look through every column in A:N, and every column in O:AP to see if there's something in it?

Every row could be considered a set and every row will have at least one word in it. A1:N1 is our range for set 1 and would all be English synonyms of each other and we can say our identical keys. Then we have O1:AP1 range which would be the Russian translation and each column within that range is the Russian word for each part of speech/case (singular nominative | singular genitive | a second variation of the singular genitive | singular dative | singular dative second variation | etc for each part of speech) Not every Russian word has a 2nd variation of spelling for the genitive case for example, so that call would be black for that set.

There are also some sets of Russian words that the database doesn't have any English translations or synonyms so for that set, columns A:N would be blank.

 

[chillzen]

I don't understand. Column P looks entirely empty, as does T and V. Column R has a smattering of words. That's what I meant by "gaps". On row 2, there is a gap in columns P, R, T, and V. That's how my macro knows how to quit looking at a row, when it encounters an empty cell. Do you want me to look through every column in A:N, and every column in O:AP to see if there's something in it?

I am working on a better example of my input/output model for this for explanation purposes and will post as soon as done.

 

[chillzen]

Input Example

Book1
	A	B	C	D	E	F	G	H
1	English	English	English	English	Russian	Russian	Russian	Russian
2	person	people	man		R1	R2		R3
3	year				R4	R5	R6	R7
4	eye
5					R8
6						R9
7	friend	pal					R10
Sheet1

Desired output

Book1
	A	B
18	English	Russian
19	person	R1
20	person	R2
21	person	R3
22	people	R1
23	people	R2
24	people	R3
25	man	R1
26	man	R2
27	man	R3
28	year	R4
29	year	R5
30	year	R6
31	year	R7
32	eye
33		R8
34		R9
35	friend	R10
36	friend	R10
Sheet1

A:D in the example represents A:P - English range
E:H in the example represents O:AP - Russian range and it's spelling variations depending on the part of speech.
Not all words have a variation for a different part of speech so it would have a blank in that spot

IF there is a value in the English range, there will not be a blank gap between English cells, but may have blanks in the right cells.
IF there are values in the Russian range, there may be a blank gap in any of the cells.

 

[chillzen]

[EDIT] Correcting range. A:N = English. O:AP = Russian

Input Example

Book1
	A	B	C	D	E	F	G	H
1	English	English	English	English	Russian	Russian	Russian	Russian
2	person	people	man		R1	R2		R3
3	year				R4	R5	R6	R7
4	eye
5					R8
6						R9
7	friend	pal					R10
Sheet1

Desired output

Book1
	A	B
18	English	Russian
19	person	R1
20	person	R2
21	person	R3
22	people	R1
23	people	R2
24	people	R3
25	man	R1
26	man	R2
27	man	R3
28	year	R4
29	year	R5
30	year	R6
31	year	R7
32	eye
33		R8
34		R9
35	friend	R10
36	friend	R10
Sheet1

A:D in the example represents A:N - English range
E:H in the example represents O:AP - Russian range and it's spelling variations depending on the part of speech.
Not all words have a variation for a different part of speech so it would have a blank in that spot

IF there is a value in the English range, there will not be a blank gap between English cells, but may have blanks in the right cells.
IF there are values in the Russian range, there may be a blank gap in any of the cells.