求助：excle中从一对多转换成多对一

[门文闵同学M]

求助：excle中从一对多转换成多对一具体图片如下
求大佬帮忙看看，数据量大



具体文件如附件:

[不懂别说哎]

用数组存数据，用Split函数分割，然后按结果要求重新填充

[脆皮大雪糕]

假设你的数据在A,B列。然后在C,D列输出结果：

Sub test()
    Dim i As Long, j As Long
    Dim tmp() As String
    For i = 2 To ActiveSheet.Range("A2").End(xlDown).Row
        tmp = Split(ActiveSheet.Cells(i, 2).Text, ",")
        For j = LBound(tmp) To UBound(tmp)
            ActiveSheet.Cells(Val(tmp(j) + 1), 3) = Str(tmp(j))
            ActiveSheet.Cells(Val(tmp(j) + 1), 4) = ActiveSheet.Cells(Val(tmp(j) + 1), 4).Text & IIf(ActiveSheet.Cells(Val(tmp(j) + 1), 4).Text = "", "", ",") & ActiveSheet.Cells(i, 1).Text
        Next
    Next
End Sub

[赵4老师]

// 求助：excle中从一对多转换成多对一
// https://bbs.csdn.net/topics/399070161
//=====输入=====
//book1.csv:
//漏洞,设备
//A,"1,2,3,4,5"
//B,"2,3,4"
//C,"1,4,5,6"
//D,"2,3,5,7,8"
//=====输出=====
//book2.csv:
//设备,漏洞
//1,"A,C"
//2,"A,B,D"
//3,"A,B,D"
//4,"A,B,C"
//5,"A,C,D"
//6,"C"
//7,"D"
//8,"D"
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
const int MAXLS=10000;
struct LS {
    char L[8];
    char S[8];
} LSs[MAXLS],ls;
char l[8];
char s[8];
int main() {
    FILE *fi=fopen("book1.csv","r");
    if (NULL==fi) {
        printf("Can not open file book1.csv\n");
        return 1;
    }
    char ln[80];
    fgets(ln,80,fi);
    char *p=strchr(ln,',');
    char fda[40];
    strncpy(fda,ln,p-ln);fda[p-ln]=0;
    char *q=strchr(p+1,'\n');
    char fdb[40];
    strncpy(fdb,p+1,q-p-1);fdb[q-p-1]=0;
    FILE *fo=fopen("book2.csv","w");
    if (NULL==fo) {
        fclose(fi);
        printf("Can not create file book2.csv\n");
        return 2;
    }
    fprintf(fo,"%s,%s\n",fdb,fda);
    int i=0;
    while (1) {
        if (NULL==fgets(ln,80,fi)) break;
        p=strchr(ln,',');
        strncpy(l,ln,p-ln);l[p-ln]=0;
        q=p+2;
        while (1) {
            p=strchr(q,',');
            if (NULL==p) {
                p=strchr(q,'"');
                if (NULL==p) {
                    printf("Format error in line %d:%s",i+1,ln);
                    fcloseall();
                    return 3;
                }
                strncpy(s,q,p-q);s[p-q]=0;
                strcpy(LSs[i].L,l);
                strcpy(LSs[i].S,s);
                i++;
                if (i>=MAXLS) {
                    printf("Too many lines( >%d lines)\n",MAXLS);
                    fcloseall();
                    return 4;
                }
                break;
            } else {
                strncpy(s,q,p-q);s[p-q]=0;
                strcpy(LSs[i].L,l);
                strcpy(LSs[i].S,s);
                i++;
                if (i>=MAXLS) {
                    printf("Too many lines( >%d lines)\n",MAXLS);
                    fcloseall();
                    return 4;
                }
                q=p+1;
            }
        }
    }
    int n=i;
//  for (i=0;i<n;i++) printf("%s - %s\n",LSs[i].L,LSs[i].S);
    int j;
    for (i=0;i<n-1;i++) {
        for (j=i+1;j<n;j++) {
            if (strcmp(LSs[i].S,LSs[j].S)>0
            || (strcmp(LSs[i].S,LSs[j].S)==0
             && strcmp(LSs[i].L,LSs[j].L)>0)) {
                ls=LSs[i];LSs[i]=LSs[j];LSs[j]=ls;
            }
        }
    }
//  for (i=0;i<n;i++) printf("%s - %s\n",LSs[i].L,LSs[i].S);
    int st=0;
    for (i=0;i<n;i++) {
        if (0==st) {
            strcpy(s,LSs[i].S);
            fprintf(fo,"%s,\"%s",s,LSs[i].L);
            st=1;
        } else if (1==st) {
            if (0==strcmp(s,LSs[i].S)) {
                fprintf(fo,",%s",LSs[i].L);
            } else {
                strcpy(s,LSs[i].S);
                fprintf(fo,"\"\n%s,\"%s",s,LSs[i].L);
            }
        }
    }
    fprintf(fo,"\"\n");
    fcloseall();
    return 0;
}